SQL Server 2019生成含零采购日期的每日采购报表求助

核心方案是先生成目标年份的完整日期序列，再通过左连接关联采购数据表，确保无采购的日期也能在报表中展示，采购数据用ISNULL处理为0。

完整查询代码 #

-- 生成全年完整日期日历的CTE
WITH DateCalendar AS (
    SELECT CAST(DATEFROMPARTS(YEAR(GETDATE()), 1, 1) AS DATE) AS CalendarDate
    UNION ALL
    SELECT DATEADD(DAY, 1, CalendarDate)
    FROM DateCalendar
    WHERE CalendarDate < DATEFROMPARTS(YEAR(GETDATE()), 12, 31)
)
-- 关联采购表并计算每日采购数据
SELECT
    dc.CalendarDate,
    ISNULL(SUM(pr.TotalAmount), 0) AS DailyTotalAmount,
    ISNULL(SUM(pr.PurchaseCount), 0) AS DailyPurchaseCount
FROM DateCalendar dc
LEFT JOIN PurchaseRecords pr 
    ON dc.CalendarDate = CAST(pr.PurchaseDate AS DATE) -- 若采购日期含时间，需转换为DATE类型
GROUP BY dc.CalendarDate
ORDER BY dc.CalendarDate
OPTION (MAXRECURSION 366); -- 覆盖闰年最多366天的递归需求

关键说明 #

1. 递归CTE生成日历：无需依赖额外的日历表，直接递归生成当年1月1日到12月31日的所有日期。
2. 左连接保留无采购日期：使用LEFT JOIN而非内连接，确保即使采购表中无对应日期的数据，日历日期仍会保留在结果中。
3. 处理空值为0：通过ISNULL函数将无采购日期的合计金额和采购数量替换为0，符合报表展示逻辑。
4. 递归次数设置：SQL Server默认递归次数上限为100，显式设置MAXRECURSION 366可覆盖全年所有日期（包括闰年）。

常见问题排查 #

如果你的现有脚本无法成功关联，大概率是以下原因：

• 使用内连接：内连接仅保留双方都有匹配数据的日期，会直接丢失无采购的日期，必须改用左连接。
• 日期类型不匹配：若采购表的PurchaseDate是DATETIME/DATETIME2类型（包含时分秒），需先转换为DATE类型再与日历日期关联，否则会因时间部分不匹配导致关联失败。
• 递归终止条件错误：检查CTE的终止条件，确保生成的日期覆盖完整的目标年份，避免日期序列不完整。

示例效果 #

假设采购表PurchaseRecords有如下数据：

查询输出会包含所有日期，无采购日期的数值为0：

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