SQL查询需求：补全无数据年份周的totalstop为0

嘿，这个需求太常见了！要补全无数据的年份周数并把totalstop设为0，核心就是先造一个包含所有你需要覆盖的年周的完整“日历表”，再和你的原始统计结果做左连接，把没匹配到的数值填0就行。

具体实现步骤 #

我给你写了完整的SQL代码，整合了你原来的查询逻辑，注释里标清楚了每一步的作用：

-- 1. 生成需要覆盖的年份列表（这里动态从你的数据里取，也可以手动指定比如2017,2018）
WITH YearList AS (
    SELECT DISTINCT YEAR(Stopdate) AS [year]
    FROM Table1
    WHERE building IN (SELECT item FROM dbo.fn_Split('A1,A2,A3,A4,A...'))
),
-- 2. 生成1到53的周数（覆盖所有年份可能的周数，毕竟有的年有53周）
WeekList AS (
    SELECT 1 AS [week]
    UNION ALL
    SELECT [week] + 1 FROM WeekList WHERE [week] < 53
),
-- 3. 组合出每个年份实际存在的所有周数（过滤掉该年份不存在的周数，比如2017年只有52周就不会出现53）
FullYearWeek AS (
    SELECT 
        yl.[year],
        wl.[week]
    FROM YearList yl
    CROSS JOIN WeekList wl
    WHERE wl.[week] <= DATEPART(WEEK, DATEFROMPARTS(yl.[year], 12, 31))
),
-- 4. 你的原始统计查询（保留原来的聚合逻辑）
OriginalData AS (
    SELECT 
        YEAR(Stopdate) AS [year],
        DATEPART(WEEK, Stopdate) AS [week], -- 注意：你原来写的是date1，如果是正确字段请改成date1
        SUM(stop) AS totalstop
    FROM Table1
    WHERE building IN (SELECT item FROM dbo.fn_Split('A1,A2,A3,A4,A...'))
    GROUP BY YEAR(Stopdate), DATEPART(WEEK, Stopdate)
)
-- 5. 左连接补全数据，用ISNULL把空值转成0
SELECT 
    fyw.[year],
    fyw.[week],
    ISNULL(od.totalstop, 0) AS totalstop
FROM FullYearWeek fyw
LEFT JOIN OriginalData od ON fyw.[year] = od.[year] AND fyw.[week] = od.[week]
ORDER BY fyw.[year], fyw.[week];

几个要注意的细节 #

• 周数规则：SQL Server默认DATEPART(WEEK)是每周从周日开始算的，如果你的业务用的是ISO周（周一为第一天，周数规则更严谨），记得把所有DATEPART(WEEK)改成DATEPART(ISO_WEEK)，同时生成FullYearWeek的时候也要对应调整。
• 固定年份范围：如果不需要动态取年份，比如只需要2017-2018，可以把YearList改成：

  YearList AS (
      SELECT 2017 AS [year] UNION ALL SELECT 2018 AS [year]
  )
  

  这样查询效率会更高。
• 字段正确性：你原来的查询里写了datepart(week,date1)，如果date1不是你要统计的日期字段（比如应该是Stopdate），记得改回正确的字段名。

内容的提问来源于stack exchange，提问作者Ulrik Svensson