解决MySQL到oracle的函数迁移问题之：在MySQL使用了GROUP_CONCAT，如何在oracle中完美替代

 这个是MySQL的SQL语句，

SELECT
    a.ID AS "id",
    a.SENSOR_NAME AS "sensorName",
    a.SENSOR_TYPE AS "sensorType",
    a.SENSOR_NO AS "sensorNo",
    a.SENSOR_MODEL AS "sensorModel",
    a.COMPANY AS "company",
    a.ORG_UNIT_ID AS "orgUnitId",
    a.DEVICE_NO AS "deviceNo",
    a.COLLECT_MIN AS "collectMin",
    a.CHARGE_USER AS "chargeUser",
    a.USER_PHONE AS "userPhone",
    a.SENSOR_ICON AS "sensorIcon",
    a.USE_STATUS AS "useStatus",
    a.RUN_STATUS AS "runStatus",
    a.COMMUNICATION_NO AS "communicationNo",
    a.PRODUCE_DATE AS "produceDate",
    a.QUALITY_DATE AS "qualityDate",
    a.SENSOR_RANGE AS "sensorRange",
    a.RANGE_START AS "rangeStart",
    IFNULL ( a.RANGE_OVER, a.SENSOR_RANGE ) AS "rangeOver",
    a.MONITOR_CONTENT AS "monitorContent",
    a.DEPTH AS "depth",
    a.INIT_VALUE_X AS "initValueX",
    a.INIT_VALUE_Y AS "initValueY",
    a.INIT_VALUE_Z AS "initValueZ",
    a.IS_MULTIPLEX AS "isMultiplex",
    a.MULTIPLEX_DATE AS "multiplexDate",
    a.CREATE_BY AS "createBy",
    a.CREATE_DATE AS "createDate",
    a.UPDATE_BY AS "updateBy",
    a.UPDATE_DATE AS "updateDate",
    a.REMARKS AS "remarks",
    a.DEL_FLAG AS "delFlag",
    a.EXT_ATTR_1 AS "extAttr1",
    a.EXT_ATTR_2 AS "extAttr2",
    a.EXT_ATTR_3 AS "extAttr3",
    a.EXT_ATTR_4 AS "extAttr4",
    a.EXT_ATTR_5 AS "extAttr5",
    IFNULL ( b.SHORT_NAME, b.NAME ) AS "orgUnitName",
    y.PROJECT_NAME AS "proShortName",
    e.LAST_DATE AS "lastDate",
    cm.`NAME` AS "companyName",
CASE
        
        WHEN a.BINDING_COUNT IS NULL 
        OR a.BINDING_COUNT <= 0 THEN
            0 ELSE 1 
            END AS "bindingStatus" 
    FROM
        SENSOR a
        LEFT JOIN ORG_UNIT b ON a.ORG_UNIT_ID = b.ID
        LEFT JOIN SENSOR_REPORT e ON a.ID = e.SENSOR_ID 
        AND e.DEL_FLAG = 0
        LEFT JOIN (
        SELECT
            a1.SENSOR_ID,
            GROUP_CONCAT( DISTINCT d.SHORT_NAME SEPARATOR ',' ) AS PROJECT_NAME 
        FROM
            POINT_SENSOR a1
            INNER JOIN MONITORING_PROJECT d ON a1.PROJECT_ID = d.ID 
        GROUP BY
            a1.SENSOR_ID 
        ) AS y ON a.id = y.SENSOR_ID
        LEFT JOIN COMPANY_MANAGE cm ON a.COMPANY = cm.ID 
    WHERE
        a.DEL_FLAG = '' 
    ORDER BY
        a.ORG_UNIT_ID,
        a.COMPANY,
    a.MONITOR_CONTENT,
    a.CREATE_DATE DESC

在oracle中执行，出错如下：

  GROUP_CONCAT( DISTINCT d.SHORT_NAME SEPARATOR ',' ) AS PROJECT_NAME 在Oracle中需要进行替换，查询得知在oracle进行列转行的函数为

于是将 GROUP_CONCAT( DISTINCT d.SHORT_NAME SEPARATOR ',' ) AS PROJECT_NAME 替换为

LISTAGG ( d.SHORT_NAME, ',' ) WITHIN GROUP ( ORDER BY d.SHORT_NAME ) AS PROJECT_NAME 

但是，在替换之后还是出现了问题

 

在一系列的搜索之下，终于解决了这个问题，原来是： 

LEFT JOIN (
        SELECT
            a1.SENSOR_ID,
            GROUP_CONCAT( DISTINCT d.SHORT_NAME SEPARATOR ',' ) AS PROJECT_NAME 
        FROM
            POINT_SENSOR a1
            INNER JOIN MONITORING_PROJECT d ON a1.PROJECT_ID = d.ID 
        GROUP BY
            a1.SENSOR_ID 
        ) AS y ON a.id = y.SENSOR_ID

这一块出现了问题，解决方案是

LEFT JOIN （SELECT
        * 
    FROM
        (
        SELECT
            a1.SENSOR_ID as id,
            listagg ( d.SHORT_NAME, ',' ) WITHIN GROUP ( ORDER BY d.SHORT_NAME ) AS projectName
        FROM
            POINT_SENSOR a1
            INNER JOIN MONITORING_PROJECT d ON a1.PROJECT_ID = d.ID 
        GROUP BY
            a1.SENSOR_ID 
        ))y ON a.id = y.id

改成这样终于完美的进行了转换。