解决MySQL到oracle的函数迁移问题之：在MySQL使⽤了
GROUP_CONCAT，。。。
这个是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",
switch语句的作用是什么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
mysql语句转oracle
0 ELSE 1
END AS "bindingStatus"
my oracle support口令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
administrator密码
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
sql update语句有返回值么resulttype是什么*
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
改成这样终于完美的进⾏了转换。
数据库设计规范及方法

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。