如何关联全月份表与分ID账单表，生成含空值的全量月度数据

问题背景 #

需要合并两个表，按ID维度重复全月份表的月度数据，对不存在的记录填充NULL。使用以下LEFT JOIN查询未达到预期效果，仅将ID 2的数据合并到ID 1的相同月份：

SELECT a.month, b.month FROM all_month a LEFT JOIN billing b ON a.month = b.month

表结构 #

表1：all_month（全月份表） #

表2：billing（账单表） #

预期结果 #

解决方案 #

原查询未按ID维度生成全量的月份组合，需要先创建每个ID对应所有月份的基础数据集，再关联账单表匹配数据。

核心思路 #

1. 获取账单表中所有唯一ID，与全月份表做交叉连接，生成每个ID的12个月记录；
2. 将上述数据集与账单表做LEFT JOIN，同时匹配ID和月份，不存在的记录自动填充NULL；
3. 按ID和月份顺序排序，得到预期结果。

实现SQL #

SELECT 
    am.month AS `MONTH`,
    ids.id AS `ID`,
    b.month AS `MONTH`
FROM 
    all_month am
CROSS JOIN 
    (SELECT DISTINCT id FROM billing) ids
LEFT JOIN 
    billing b ON am.month = b.month AND ids.id = b.id
ORDER BY 
    ids.id, 
    CASE am.month
        WHEN 'JANUARY' THEN 1
        WHEN 'FEBRUARY' THEN 2
        WHEN 'MARCH' THEN 3
        WHEN 'APRIL' THEN 4
        WHEN 'MAY' THEN 5
        WHEN 'JUNE' THEN 6
        WHEN 'JULY' THEN 7
        WHEN 'AUGUST' THEN 8
        WHEN 'SEPTEMBER' THEN 9
        WHEN 'OCTOBER' THEN 10
        WHEN 'NOVEMBER' THEN 11
        WHEN 'DECEMBER' THEN 12
    END;

关键说明 #

• CROSS JOIN：确保每个ID都能对应到所有月份，构建完整的基础数据框架；
• 多条件LEFT JOIN：同时匹配ID和月份，避免不同ID的月份数据交叉匹配；
• 月份排序：通过CASE语句将月份名称转换为数字排序，保证结果按自然月份顺序展示，可根据数据库特性替换为更简便的函数（如MySQL的STR_TO_DATE(am.month, '%M')）。

内容的提问来源于stack exchange，提问作者John Ermy Arbigoso