MSSQL2008 PIVOT 注意

IF OBJECT_ID('tempdb..#tmp_mx') IS NOT NULL
    DROP TABLE #tmp_mx
GO

SELECT *
INTO  #tmp_mx
FROM(
SELECT 'kh0011' AS khmc, 28.0 AS kdje, 'S' AS ms, 10 AS sl
UNION ALL
SELECT 'kh0011' AS khmc, 28.0 AS kdje, 'M' AS ms, 10 AS sl
) a

SELECT khmc,SUM(kdje),SUM([S]) AS [S],SUM([M]) AS [m]
FROM #tmp_mx 
PIVOT
( sum(sl)
for ms in ([S],[M])
) as pvt 
GROUP BY khmc

最后结果是：这里的kdje 是 28 不是我们想要的56，这其实不是错误。但使用中都认为跟我们自己手写case when一样

khmc (无列名) S m
kh0011 28.0 10 10

手写case when

SELECT khmc,SUM(kdje) AS je
,SUM(CASE WHEN ms='S' THEN sl ELSE 0 END) AS [S]
,SUM(CASE WHEN ms='M' THEN sl ELSE 0 END) AS [M]
FROM #tmp_mx
GROUP BY khmc

在用PIVOT时不要再去sum count 任何列了，得到的结果是去重后的。max min则不影响