如何将PIVOT SQL转换为CASE WHEN？解决转换后统计异常问题

直接用CASE WHEN结合COUNT聚合，配合GROUP BY分组即可实现需求，同时避免PIVOT的性能问题：

SELECT
    cmdocumentdefn.code,
    cmdocumentdefn.description,
    -- 按工作日分别统计数量
    COUNT(CASE WHEN DATEPART(weekday, cmrevisionaddress.issueddate) = 2 THEN 1 END) AS MON,
    COUNT(CASE WHEN DATEPART(weekday, cmrevisionaddress.issueddate) = 3 THEN 1 END) AS TUE,
    COUNT(CASE WHEN DATEPART(weekday, cmrevisionaddress.issueddate) = 4 THEN 1 END) AS WED,
    COUNT(CASE WHEN DATEPART(weekday, cmrevisionaddress.issueddate) = 5 THEN 1 END) AS THU,
    COUNT(CASE WHEN DATEPART(weekday, cmrevisionaddress.issueddate) = 6 THEN 1 END) AS FRI,
    COUNT(CASE WHEN DATEPART(weekday, cmrevisionaddress.issueddate) = 7 THEN 1 END) AS SAT,
    COUNT(CASE WHEN DATEPART(weekday, cmrevisionaddress.issueddate) = 1 THEN 1 END) AS SUN
FROM cmdocumentdefn
INNER JOIN cmdocument
    ON cmdocumentdefn.cmdocumentdefn = cmdocument.cmdocumentdefn
INNER JOIN cmrevisionaddress
    ON cmdocument.cmdocument = cmrevisionaddress.cmdocument
WHERE cmdocumentdefn.code = 'AF'
GROUP BY cmdocumentdefn.code, cmdocumentdefn.description

关键说明 #

• 每个CASE WHEN仅在日期匹配对应工作日时返回1，否则返回NULL，COUNT会自动忽略NULL值，只统计符合条件的记录数
• 必须添加GROUP BY，按code和description分组，确保统计的是每组内各工作日的数量，而非全局总计数
• 这种写法在大数据量场景下比PIVOT更高效，避免了子查询和PIVOT转换的额外开销

内容的提问来源于stack exchange，提问作者Leonardo Vicente Rapirap