SQL中如何实现表无对应记录时为每个周分组返回结果行

现状说明 #

• 需求背景：需要按双周维度对任务记录做分组聚合，暂未找到直接实现路径，目前先通过DATEPART("ww", 日期字段)提取周数、搭配年份做基础分组，后续计划通过循环累加连续两周的统计值得到双周维度结果。
• 当前卡点：某一周在任务表中没有对应记录时，查询不会返回该周的结果行，无法直接累加相邻两周的统计值。目标是即使数据库中某周不存在相关记录，查询也能返回对应周的行。
• 现有查询语句：

SELECT 
    SUM(T.task_minutes) AS TASK_TIME,
    (SELECT SUM(hours_limit)
     FROM project_hour_monitors
     WHERE project_id = MAX(P.project_id)) AS TOTAL_HOURS_CAP,
    DATEPART(week, T.task_date),
    DATEPART(year, T.TASK_DATE)
FROM   
    PROJECTS AS P
LEFT JOIN 
    tasks AS T ON T.project_id = P.project_id
WHERE  
    P.project_id = 18155
    AND T.task_date >= P.date_posted
    AND T.task_date <= P.due_date
    AND T.monitor_id IS NOT NULL
    AND T.monitor_id <> 0
GROUP BY 
    DATEPART(week, T.task_date), DATEPART(year, T.TASK_DATE)
ORDER BY 
    DATEPART(year, T.TASK_DATE), DATEPART(week, T.task_date)

问题根因 #

• 现有查询的聚合逻辑是以tasks表中实际存在的日期为基准，没有提前生成覆盖项目全周期的连续周序列作为驱动表，自然会遗漏没有任务记录的周。
• 原有LEFT JOIN逻辑失效：把tasks表的过滤条件写在了WHERE子句中，会直接过滤掉tasks表为NULL的行（也就是没有任务的周对应的项目行），等同于退化成了INNER JOIN。

修正方案 #

采用连续周序列作为驱动表，左关联任务表做聚合，保证项目全周期的周数无遗漏，核心步骤：

• 先提取目标项目的时间边界，计算序列的起止对齐周
• 递归生成覆盖全时间范围的连续周序列
• 将任务表关联到周序列上，空周的聚合值直接返回0

修正后参考代码（适配SQL Server环境） #

-- 提取目标项目的时间范围边界
WITH project_scope AS (
    SELECT 
        project_id,
        date_posted,
        due_date,
        -- 统一以每周周一为对齐基准，避免跨年周计数错位
        DATEADD(week, DATEDIFF(week, 0, date_posted), 0) AS week_start,
        DATEADD(week, DATEDIFF(week, 0, due_date), 0) AS week_end
    FROM PROJECTS
    WHERE project_id = 18155
),
-- 递归生成连续周序列
week_series AS (
    SELECT 
        week_start AS current_week_start,
        DATEADD(day, 6, week_start) AS current_week_end,
        YEAR(week_start) AS week_year,
        DATEPART(week, week_start) AS week_num
    FROM project_scope
    UNION ALL
    SELECT 
        DATEADD(week, 1, current_week_start),
        DATEADD(week, 1, current_week_end),
        YEAR(DATEADD(week, 1, current_week_start)),
        DATEPART(week, 1, DATEADD(week, 1, current_week_start))
    FROM week_series
    WHERE current_week_start < (SELECT week_end FROM project_scope)
)
-- 左关联任务表做聚合，空周自动补0
SELECT 
    ISNULL(SUM(T.task_minutes), 0) AS TASK_TIME,
    (SELECT SUM(hours_limit) FROM project_hour_monitors WHERE project_id = 18155) AS TOTAL_HOURS_CAP,
    ws.week_num,
    ws.week_year
FROM week_series ws
LEFT JOIN tasks T
    ON T.task_date BETWEEN ws.current_week_start AND ws.current_week_end
    AND T.project_id = 18155
    -- 所有任务表的过滤条件放到JOIN的ON子句中，保证左连接生效
    AND T.monitor_id IS NOT NULL 
    AND T.monitor_id <> 0
GROUP BY ws.week_num, ws.week_year, ws.current_week_start
ORDER BY ws.week_year, ws.week_num
OPTION (MAXRECURSION 1000) -- 规避项目周期过长时的递归层数限制

后续双周聚合实现说明 #

• 基于生成好的连续周结果集，直接按week_year + FLOOR((week_num - 1)/2)的规则分组，对TASK_TIME做累加即可得到双周维度统计值，不会因为空周缺失导致计算错位。

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