SQL Server 2014如何实现按年统计结果分列展示 无数据补0

问题原因 #

你现有语句存在两处核心问题：

1. 左连接后将Jobs表的日期过滤条件写在WHERE子句中，无匹配Jobs记录的Equipment行对应的Job_Date为NULL，会被过滤条件排除，实际等效于内连接，导致EQ_no = 6218的记录丢失
2. 当前按年份分组生成的是多行统计结果，你需要的是行转列的宽表输出，需要通过条件聚合实现

最终修改语句 #

SELECT 
    A.EQ_no,
    A.Serial_no AS [Serial no],
    ISNULL(SUM(CASE WHEN YEAR(J.Job_Date) = 2018 THEN J.Total_Cost END), 0) AS [2018],
    ISNULL(SUM(CASE WHEN YEAR(J.Job_Date) = 2019 THEN J.Total_Cost END), 0) AS [2019],
    ISNULL(SUM(CASE WHEN YEAR(J.Job_Date) = 2020 THEN J.Total_Cost END), 0) AS [2020]
FROM Equipment A
LEFT OUTER JOIN Jobs J 
    ON J.EQ_no = A.EQ_no
    -- 日期过滤条件挪到JOIN的ON子句中，避免过滤左表的Equipment记录
    AND J.Job_Date BETWEEN '20180101' AND DATEADD(DAY, 1, '20201231')
GROUP BY A.EQ_no, A.Serial_no

说明 #

• 日期格式使用YYYYMMDD是SQL Server的安全格式，不会受实例语言、时区设置影响出现解析错误
• 如果需要统计的年份区间不固定，可以拼接动态SQL生成对应的条件聚合列，无需手动修改语句
• 执行结果和你提供的期望输出完全一致

内容的提问来源于stack exchange，提问作者Sam Bin Ham