SQL如何补全[年.月 周]格式周序列中的缺失周数缺口

原SQL直接从MONITORING业务表分组取周标识，只会返回存在业务数据的周条目，没有数据的周就会出现断档。要补全缺失周，不能直接从业务表取维度值，核心是先生成指定时间范围内完整的连续周序列，再关联业务表数据。

DECLARE
    @startdate datetime,
    @enddate datetime,
    @paramdef nvarchar(max)

SET @startdate = '2021-01-01 00:00:00.000';
SET @enddate = GETDATE();
SET @paramdef = '@startdate datetime, @enddate datetime';

-- 递归生成时间范围内所有连续周
WITH AllWeeks AS (
    -- 锚点：取起始日期所在周的周一，若原表周统计从周日开始，把末尾的0改成-1即可
    SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, @startdate), 0) AS WeekStart
    UNION ALL
    SELECT DATEADD(WEEK, 1, WeekStart)
    FROM AllWeeks
    WHERE WeekStart < DATEADD(WEEK, DATEDIFF(WEEK, 0, @enddate), 0)
),
-- 转换为和原表一致的「年.月 周」格式，周数按自然年累计，和示例规则匹配
WeekDim AS (
    SELECT
        CONCAT(
            CAST(YEAR(WeekStart) AS VARCHAR(4)),
            '.',
            RIGHT('0' + CAST(MONTH(WeekStart) AS VARCHAR(2)),2),
            ' ',
            DATEDIFF(WEEK, DATEADD(YEAR, DATEDIFF(YEAR, 0, WeekStart), 0), WeekStart) + 1
        ) AS S_FORMATTED_WEEK_MONTH
    FROM AllWeeks
),
-- 提取业务表中已存在的周数据
ExistingWeeks AS (
    SELECT S_FORMATTED_WEEK_MONTH
    FROM MONITORING
    WHERE S_DATE_TO >= @startdate AND S_DATE_TO <= @enddate
    GROUP BY S_FORMATTED_WEEK_MONTH
)
-- 拼接最终字符串，全量周序列左关联业务数据，无断档
SELECT STUFF
(
    (
        SELECT 
            -- 如果需要直接输出带加粗标记的补全项，把下面一行替换成注释里的逻辑即可
            -- CASE WHEN e.S_FORMATTED_WEEK_MONTH IS NULL THEN ',**' + QUOTENAME(w.S_FORMATTED_WEEK_MONTH) + '**' ELSE ',' + QUOTENAME(w.S_FORMATTED_WEEK_MONTH) END
            ',' + QUOTENAME( w.S_FORMATTED_WEEK_MONTH )
        FROM 
            WeekDim w
            LEFT JOIN ExistingWeeks e ON w.S_FORMATTED_WEEK_MONTH = e.S_FORMATTED_WEEK_MONTH
        ORDER BY 
            CAST( REPLACE( SUBSTRING(w.S_FORMATTED_WEEK_MONTH, 1, 7), '.', '' ) AS INT ),
            CAST( REPLACE( SUBSTRING(w.S_FORMATTED_WEEK_MONTH, 8, 9), '.', '' ) AS INT )
        FOR XML PATH(''), TYPE 
    ).value('.', 'NVARCHAR(MAX)')  ,1,1,''
)
-- 递归上限设为1000，可覆盖近20年的周范围，满足绝大多数场景
OPTION (MAXRECURSION 1000);

• 代码默认周起始日为周一，如果原表周统计规则以周日为起始，调整AllWeeks锚点的偏移值即可
• 周数计算默认按自然年累计，和你给出的示例（2022年2月对应第10周）规则完全对齐，如果原表周数统计逻辑有差异，调整WeekDim中的周数计算部分和原表规则保持一致即可
• 代码中预留了补全项加粗的逻辑开关，替换对应行代码就能直接生成你示例中带**标记补全项的结果

内容的提问来源于stack exchange，提问作者Joe The Pogi