Firebird查询：如何为缺失数据的月份生成计数为0的行？

要让Firebird返回全年12个月份的统计结果，哪怕某个月份没有数据也要显示0，你需要先构造一个包含所有12个月的虚拟数据集，再和你的统计查询做左连接。

修改后的完整SQL查询 #

WITH Months AS (
    SELECT 
        1 AS month_num, 'January' AS month_name FROM rdb$database
    UNION ALL SELECT 2, 'February' FROM rdb$database
    UNION ALL SELECT 3, 'March' FROM rdb$database
    UNION ALL SELECT 4, 'April' FROM rdb$database
    UNION ALL SELECT 5, 'May' FROM rdb$database
    UNION ALL SELECT 6, 'June' FROM rdb$database
    UNION ALL SELECT 7, 'July' FROM rdb$database
    UNION ALL SELECT 8, 'August' FROM rdb$database
    UNION ALL SELECT 9, 'September' FROM rdb$database
    UNION ALL SELECT 10, 'October' FROM rdb$database
    UNION ALL SELECT 11, 'November' FROM rdb$database
    UNION ALL SELECT 12, 'December' FROM rdb$database
),
MonthlyStats AS (
    SELECT 
        EXTRACT(MONTH FROM pd.Date) AS month_num,
        COUNT(DISTINCT pd.numbers) AS count
    FROM Power_D pd 
    WHERE EXTRACT(YEAR FROM pd.Date) = 2016 
      AND pd.Name = 'Test' 
    GROUP BY EXTRACT(MONTH FROM pd.Date)
)
SELECT 
    m.month_name AS "MONTH",
    COALESCE(ms.count, 0) AS "Count"
FROM Months m
LEFT JOIN MonthlyStats ms ON m.month_num = ms.month_num
ORDER BY m.month_num;

代码说明 #

1. Months 公共表表达式：生成包含12个月份数字和对应名称的固定数据集，确保所有月份都被包含。
2. MonthlyStats 公共表表达式：保留你原来的统计逻辑，只提取月份数字和对应计数，方便后续连接。
3. 左连接与COALESCE函数：用左连接确保所有月份都出现在结果中，COALESCE将没有匹配到的计数（NULL）替换为0。

最终返回结果 #

January     0
February    330
March       212
April       178
May         222
June        487
July        695
August      433
September   757
October     1307
November    321
December    143

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