SQL Server如何补全日期范围缺失日期并填充数量为0？

要让结果包含指定日期范围内的所有日期，缺失日期的数量填充为0，核心是先构造覆盖目标范围的完整日期序列，再与原查询结果做左连接。以下是两种常用实现方式：

方法一：用递归CTE生成日期序列 #

递归CTE可以动态生成指定范围内的所有日期，无需依赖额外表：

WITH DateRange AS (
    -- 起始日期
    SELECT CAST('2024-06-01' AS DATE) AS date
    UNION ALL
    -- 递归生成后续日期，直到结束日期
    SELECT DATEADD(DAY, 1, date)
    FROM DateRange
    WHERE date < CAST('2024-08-09' AS DATE)
)
SELECT 
    dr.date,
    COALESCE(p.quantity, 0) AS quantity
FROM DateRange dr
LEFT JOIN (
    -- 原查询逻辑，聚合每日数量
    SELECT 
        CAST(from_time AS DATE) AS date,
        SUM(quantity) AS quantity
    FROM planned
    WHERE from_time >= '2024-06-01 00:00:00.000'
      AND from_time <= '2024-08-09 00:00:00.000'
      AND line = 'Line1'
      AND PlantName = 'Luminous'
    GROUP BY CAST(from_time AS DATE)
) p ON dr.date = p.date
ORDER BY dr.date
OPTION (MAXRECURSION 0); -- 若日期范围超过100天，需添加此选项解除递归次数限制

关键点说明 #

• DateRange CTE会生成从起始日到结束日的所有连续日期，包含首尾。
• 左连接确保所有日期都出现在结果中，即使原表无对应数据。
• COALESCE(p.quantity, 0) 将缺失的数量值替换为0。

方法二：使用日期维度表（推荐长期使用） #

如果数据库中已有现成的日期维度表（通常包含多年连续日期），直接使用会更高效：

SELECT 
    dt.date,
    COALESCE(p.quantity, 0) AS quantity
FROM DateTable dt
LEFT JOIN (
    SELECT 
        CAST(from_time AS DATE) AS date,
        SUM(quantity) AS quantity
    FROM planned
    WHERE from_time >= '2024-06-01 00:00:00.000'
      AND from_time <= '2024-08-09 00:00:00.000'
      AND line = 'Line1'
      AND PlantName = 'Luminous'
    GROUP BY CAST(from_time AS DATE)
) p ON dt.date = p.date
WHERE dt.date BETWEEN '2024-06-01' AND '2024-08-09'
ORDER BY dt.date;

优势 #

• 日期维度表预先生成，性能比递归CTE更优，尤其在处理大范围日期时。
• 可扩展更多日期属性（如星期、月份、季度等），满足复杂报表需求。

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