我必须将以下MSSQL转换为MySQL时遇到问题。我有两个主要问题。
我尝试删除MAX，然后将其更改为WU1 =（当rk = 1 THEN WU END时），但这是行不通的。
代码如下：
SELECT ReqN,
MAX(CASE WHEN rk = 1 THEN WU END) AS WU1,
MAX(CASE WHEN rk = 2 THEN WU END) AS WU2,
MAX(CASE WHEN rk = 3 THEN WU END) AS WU3,
MAX(CASE WHEN rk = 4 THEN WU END) AS WU4,
MAX(CASE WHEN rk = 5 THEN WU END) AS WU5,
MAX(CASE WHEN rk = 6 THEN WU END) AS WU6,
MAX(CASE WHEN rk = 7 THEN WU END) AS WU7,
MAX(CASE WHEN rk = 8 THEN WU END) AS WU8
into #tblWU
FROM (SELECT ReqN, WUnit,
(SELECT COUNT(*)
FROM tblDevWU AS B
WHERE B.ReqN = A.ReqN
AND B.WUnit <= A.WUnit) AS rk
FROM tblDevWU AS A) as A
GROUP BY ReqN;
Mysql的等效项是：
CREATE TEMPORARY TABLE tblWU AS
SELECT ReqN,
MAX(IF(rk=1, WU, NULL)) WU1,
MAX(IF(rk=2, WU, NULL)) WU2,
MAX(IF(rk=3, WU, NULL)) WU3,
MAX(IF(rk=4, WU, NULL)) WU4,
MAX(IF(rk=5, WU, NULL)) WU5,
MAX(IF(rk=6, WU, NULL)) WU6,
MAX(IF(rk=7, WU, NULL)) WU7,
MAX(IF(rk=8, WU, NULL)) WU8
FROM (
SELECT ReqN, WUnit,
(
SELECT COUNT(*) FROM tblDevWU AS B
WHERE B.ReqN = A.ReqN AND B.WUnit <= A.WUnit
) AS rk
FROM tblDevWU AS A) as A
GROUP BY ReqN;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句