MySQL转Oracle、达梦（DM）

1.$\color{green}{if}$

-- MySQL
IF ( ifnull( wp.photoId, '' ) = '', 0, 1 ) AS hasPhoto
-- Oracle
CASE WHEN ( ifnull( wp.photoId, '' ) ) = '' THEN 0 ELSE 1 END hasPhoto

2.$\color{green}{group\_concat}$

-- MySQL
GROUP_CONCAT( dicName SEPARATOR ',' )默认以 ',' 分隔 --> 简写 GROUP_CONCAT( dicName )
GROUP_CONCAT( dicName SEPARATOR '_' )
-- 去重且排序
GROUP_CONCAT( DISTINCT t2.orgName ORDER BY t2.provinceCode, t2.cityCode SEPARATOR '<br>' ) AS testUnitName
-- Oracle
WM_CONCAT(dicName SEPARATOR ',') --> 简写 WM_CONCAT( dicName )
REPLACE(WM_CONCAT(dicName), ',', '_')
-- 去重且排序
WM_CONCAT( DISTINCT t2.orgName) over(partition BY wmpId ,unitId ) AS testUnitName  单独使用不支持order by
listagg(t2.orgName, '<br>' ) within group(order by t2.provinceCode, t2.cityCode) AS  testUnitName 单独使用不支持distinct

3.$\color{green}{除0错误}$

ifnull( b.comtest, 0 ) * 100.0 / ( ifnull( b.allsample, 0 ) )
替换 
decode( ifnull( b.allsample, 0 ) , 0, 0, ifnull( b.comtest, 0 ) * 100.0 /  ifnull( b.allsample, 0 )  )

4.$\color{green}{case\_when}$

-- MySQL
CASE
	WHEN a.islabParallel = 0 THEN b.itemId = d.itemId
	ELSE c.itemId = d.itemId
END
-- Oracle
d.itemId = (CASE WHEN a.islabParallel = 0 THEN b.itemId ELSE c.itemId END)

5.$\color{green}{对多个字段中的某一个字段去重}$

-- 1. 对methodName,methodId同时去重
SELECT DISTINCT methodName,methodId
FROM work_monitoringmethod
<=>
SELECT methodName,methodId
FROM work_monitoringmethod
GROUP BY 
methodName,methodId
-- 2 对单个methodName去重
-- 2.1 MySQL可以通过,且结果正确
SELECT methodName,methodId
FROM work_monitoringmethod
GROUP BY 
methodName
-- 2.2 DM报错 不是 GROUP BY 表达式,所以需要加一个methodId(但是不能完全去重)
SELECT methodName,methodId
FROM work_monitoringmethod
GROUP BY 
methodName,methodId
-- 所以需要采用据聚合函数
SELECT DISTINCT methodName,MAX(methodId) 
FROM work_monitoringmethod
GROUP BY methodName