年中展示全年数据：未到月份以0填充的SQL实现需求

需求 #

• 年中阶段需展示全年12个月的数据
• 当年未到来的月份，new_total和all_total字段以0填充
• 无需手动添加月份，进入对应月份后自动显示该月实际数据

现有SQL语句 #

SELECT
FORMAT(a.CreatedDateTime, 'MMM') AS 'month', 
SUM(b.NewContactCount) AS 'new_total', 
SUM(b.TotalContactCount) AS 'all_total' 
FROM database.dbo.ImportBatch a 
LEFT JOIN ImportBatch_File b ON a.id = b.BatchID 
WHERE a.CreatedDateTime >= CAST(YEAR(GETDATE()) AS VARCHAR) + '/01/01' 
GROUP BY FORMAT(a.CreatedDateTime, 'MMM'), MONTH(a.CreatedDateTime) 
ORDER BY MONTH(a.CreatedDateTime)

当前查询结果 #

仅返回有实际数据的月份：

month     new_total   all_total
 1  Jan       337         1000
 2  Feb       146         497

期望输出 #

包含全年12个月，未到月份数值自动填充为0：

month     new_total   all_total
 1  Jan       337         1000
 2  Feb       146         497
 3  Mar       0           0
 4  Apr       0           0
 5  May       0           0
 6  Jun       0           0
 7  Jul       0           0
 8  Aug       0           0
 9  Sep       0           0
10  Oct       0           0
11  Nov       0           0
12  Dec       0           0

解决方案 #

核心思路是先生成全年12个月的基础列表，再与原有查询结果进行左连接，用ISNULL将空值替换为0。

方法1：CTE手动生成月份列表 #

WITH AllMonths AS (
    SELECT 1 AS MonthNum, 'Jan' AS MonthName
    UNION SELECT 2, 'Feb'
    UNION SELECT 3, 'Mar'
    UNION SELECT 4, 'Apr'
    UNION SELECT 5, 'May'
    UNION SELECT 6, 'Jun'
    UNION SELECT 7, 'Jul'
    UNION SELECT 8, 'Aug'
    UNION SELECT 9, 'Sep'
    UNION SELECT 10, 'Oct'
    UNION SELECT 11, 'Nov'
    UNION SELECT 12, 'Dec'
)
SELECT
    am.MonthName AS 'month',
    ISNULL(SUM(b.NewContactCount), 0) AS 'new_total',
    ISNULL(SUM(b.TotalContactCount), 0) AS 'all_total'
FROM AllMonths am
LEFT JOIN (
    SELECT
        MONTH(a.CreatedDateTime) AS MonthNum,
        b.NewContactCount,
        b.TotalContactCount
    FROM database.dbo.ImportBatch a
    LEFT JOIN ImportBatch_File b ON a.id = b.BatchID
    WHERE a.CreatedDateTime >= DATEFROMPARTS(YEAR(GETDATE()), 1, 1)
) data ON am.MonthNum = data.MonthNum
GROUP BY am.MonthNum, am.MonthName
ORDER BY am.MonthNum

方法2：用GENERATE_SERIES生成月份（SQL Server 2022+适用） #

WITH AllMonths AS (
    SELECT
        value AS MonthNum,
        FORMAT(DATEFROMPARTS(YEAR(GETDATE()), value, 1), 'MMM') AS MonthName
    FROM GENERATE_SERIES(1, 12)
)
SELECT
    am.MonthName AS 'month',
    ISNULL(SUM(b.NewContactCount), 0) AS 'new_total',
    ISNULL(SUM(b.TotalContactCount), 0) AS 'all_total'
FROM AllMonths am
LEFT JOIN (
    SELECT
        MONTH(a.CreatedDateTime) AS MonthNum,
        b.NewContactCount,
        b.TotalContactCount
    FROM database.dbo.ImportBatch a
    LEFT JOIN ImportBatch_File b ON a.id = b.BatchID
    WHERE a.CreatedDateTime >= DATEFROMPARTS(YEAR(GETDATE()), 1, 1)
) data ON am.MonthNum = data.MonthNum
GROUP BY am.MonthNum, am.MonthName
ORDER BY am.MonthNum

关键说明 #

• 以月份列表作为主表左连接数据，确保所有月份都被保留
• ISNULL函数自动将无数据月份的数值替换为0
• 使用DATEFROMPARTS替代字符串拼接，避免日期格式兼容问题
• 进入对应月份后，只要数据库存在该月数据，查询会自动返回实际数值，无需修改SQL

内容的提问来源于stack exchange，提问作者a.WOL7