Firebird SQL查询需显示无记录月份的0值结果求助

要让无记录的月份显示0，你需要先构造一个包含12个月份的完整列表，再和你的统计数据做左连接，确保所有月份都能被返回。以下是修改后的SQL：

WITH MONTH_LIST 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
)
SELECT 
    ml.MONTH_NAME AS "MONTH",
    COALESCE(COUNT(DISTINCT pd.numbers), 0) AS "Count"
FROM MONTH_LIST ml
LEFT JOIN Power_D pd 
    ON EXTRACT(MONTH FROM pd.Date) = ml.MONTH_NUM
    AND EXTRACT(YEAR FROM pd.Date) = 2016
    AND pd.Name = 'Test'
GROUP BY ml.MONTH_NUM, ml.MONTH_NAME
ORDER BY ml.MONTH_NUM;

关键说明： #

• MONTH_LIST：通过UNION ALL生成包含12个月份的虚拟表，每个月份对应数字和名称，确保全年月份都被覆盖。
• 左连接（LEFT JOIN）：以月份列表为基础，关联业务表Power_D，即使某个月份没有匹配数据，也会保留该月份的行。
• COALESCE函数：将无匹配时的NULL统计值转为0，符合显示需求。
• 排序：按月份数字排序，保证结果按自然月份顺序展示。

执行后会得到包含所有12个月的统计结果：

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