如何在SQL中扩展数据集，为每个类别添加全年月份列？

需求说明：现有包含category和value列的表，需要为每个类别关联全年12个月份，生成每个类别对应每个月份的行记录，value保持原类别对应的值。

通用思路 #

先生成包含全年12个月份的临时列表，再与原表执行笛卡尔积关联（CROSS JOIN），即可得到每个类别对应所有月份的结果。

以下是不同SQL方言的具体实现：

MySQL 实现 #

SELECT 
    t.category,
    m.month_name,
    t.value
FROM 
    your_table t  -- 替换为你的实际表名
CROSS JOIN 
    (SELECT 'January' AS month_name UNION ALL
     SELECT 'February' UNION ALL
     SELECT 'March' UNION ALL
     SELECT 'April' UNION ALL
     SELECT 'May' UNION ALL
     SELECT 'June' UNION ALL
     SELECT 'July' UNION ALL
     SELECT 'August' UNION ALL
     SELECT 'September' UNION ALL
     SELECT 'October' UNION ALL
     SELECT 'November' UNION ALL
     SELECT 'December') m
ORDER BY 
    t.category, 
    FIELD(m.month_name, 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December');

• 用UNION ALL手动生成月份列表
• FIELD()函数用于保证结果按自然月份顺序排序

PostgreSQL 实现 #

SELECT 
    t.category,
    TO_CHAR(to_date(m.month_num::text, 'MM'), 'FMMonth') AS month_name,
    t.value
FROM 
    your_table t  -- 替换为你的实际表名
CROSS JOIN 
    generate_series(1, 12) AS m(month_num)
ORDER BY 
    t.category, m.month_num;

• generate_series(1,12)生成1-12的月份数字
• TO_CHAR()将数字转换为完整月份名称，FMMonth用于去除名称后的多余空格

SQL Server 实现 #

SELECT 
    t.category,
    m.month_name,
    t.value
FROM 
    your_table t  -- 替换为你的实际表名
CROSS JOIN 
    (VALUES ('January'), ('February'), ('March'), ('April'), ('May'), ('June'),
            ('July'), ('August'), ('September'), ('October'), ('November'), ('December')) AS m(month_name)
ORDER BY 
    t.category, 
    CASE m.month_name
        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;

• 用VALUES子句直接定义月份列表
• CASE语句用于指定月份的排序逻辑

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