56,682
社区成员
发帖
与我相关
我的任务
分享
/****** Script for SelectTopNRows command from SSMS ******/
SELECT S.*, D.ADMOH,D.PROH
FROM E85_STATIONERYLIST S,
(SELECT NAME, SUM([ADMOH]) AS ADMOH,SUM([PROH]) AS PROH
FROM (
SELECT A.NAME,sum(B.Quantity) AS OH,'ADMOH' AS TYPE,'ADMPN' AS PN
FROM E85_StationeryList A LEFT JOIN [RR_SubInvOnHand_Current] B
ON B.Part = A.PARTNO_ADM
GROUP BY A.NAME
UNION
SELECT A.NAME,SUM(B.Quantity) AS OH,'PROH' AS TYPE,'PRPN' AS PN
FROM E85_StationeryList A LEFT JOIN [RR_SubInvOnHand_Current] B
ON B.Part = A.PARTNO_PR
GROUP BY A.NAME
) AS C
PIVOT (
SUM(OH)
FOR TYPE IN (
[ADMOH],
[PROH]
)
) AS P
GROUP BY NAME
) AS D
WHERE S.NAME = D.NAME
ORDER BY S.ID
各位看官,帮忙把下面的sqlserver的语句改成Mysql可以执行的,原因是Mysql不支持PivotSELECT S.* ,
T1.ADMOH ,
T2.PROH
FROM E85_STATIONERYLIST S
LEFT JOIN ( SELECT A.NAME ,
SUM(B.Quantity) AS ADMOH
FROM E85_StationeryList A
LEFT JOIN [RR_SubInvOnHand_Current] B ON B.Part = A.PARTNO_ADM
GROUP BY A.NAME
) AS T1 ON T1.NAME = S.NAME
LEFT JOIN ( SELECT A.NAME ,
SUM(B.Quantity) AS PROH
FROM E85_StationeryList A
LEFT JOIN [RR_SubInvOnHand_Current] B ON B.Part = A.PARTNO_PR
GROUP BY A.NAME
) AS T2 ON T2.NAME = S.NAME
WHERE T1.NAME IS NOT NULL
OR T2.NAME IS NOT NULL
ORDER BY S.ID;