如何用SQL创建可根据当前年月动态调整列顺序的表

实现核心逻辑 #

你需要的动态列效果无法通过纯静态SQL实现，因为静态SQL的返回列名必须预先定义，无法跟随当前时间自动更新。结合你每月更新一次结果表的需求，推荐以下两种可落地的方案：

方案1：定时调度生成静态宽表（最适配需求） #

你是每月固定更新一次结果表，直接写调度脚本每月初执行一次即可，步骤如下：

• 第一步：先清空/重建目标结果表
• 第二步：先查询出最近12个月的月份列表，按倒序排列，用这个列表拼接生成建表+插入数据的SQL语句
• 第三步：执行拼接好的行转列逻辑，把数据写入结果表

以下是MySQL语法示例，其他数据库仅需调整日期函数即可通用：
首先查询最近12个月的月份列表：

SELECT DATE_FORMAT(date_sub(CURRENT_DATE, INTERVAL (n-1) MONTH), '%Y年%m月') as month_name,
       DATE_FORMAT(date_sub(CURRENT_DATE, INTERVAL (n-1) MONTH), '%Y-%m') as month_val
FROM (SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
      UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
      UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) t
ORDER BY month_val DESC;

用上面的结果拼接行转列SQL，最终执行的SQL示例如下：

CREATE OR REPLACE TABLE company_credit_12m AS
SELECT 
    `Company name` as 公司名称,
    MAX(CASE WHEN `Month` = '2024-05' THEN `Value` END) AS `2024年05月`,
    MAX(CASE WHEN `Month` = '2024-04' THEN `Value` END) AS `2024年04月`,
    -- 按上面的月份列表依次拼接剩下10个月的CASE语句
    MAX(CASE WHEN `Month` = '2023-06' THEN `Value` END) AS `2023年06月`
FROM source_credit_table
WHERE `Month` >= DATE_FORMAT(date_sub(CURRENT_DATE, INTERVAL 11 MONTH), '%Y-%m')
GROUP BY `Company name`;

如果使用Hive/Spark SQL这类大数据组件，也可以用PIVOT语法简化CASE语句，逻辑完全一致。

方案2：动态SQL即时查询 #

如果需要随时查询最新的12个月数据，不需要落表，可以用存储过程封装动态SQL，MySQL示例如下：

DELIMITER //
CREATE PROCEDURE get_company_credit_12m()
BEGIN
    -- 拼接行转列的CASE语句
    SET @case_sql = (
        SELECT GROUP_CONCAT(
            'MAX(CASE WHEN `Month` = \'', month_val, '\' THEN `Value` END) AS `', month_name, '`'
            ORDER BY month_val DESC SEPARATOR ',\n'
        )
        FROM (
            SELECT DATE_FORMAT(date_sub(CURRENT_DATE, INTERVAL (n-1) MONTH), '%Y年%m月') as month_name,
                   DATE_FORMAT(date_sub(CURRENT_DATE, INTERVAL (n-1) MONTH), '%Y-%m') as month_val
            FROM (SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
                  UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
                  UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) t
        ) t
    );
    -- 拼接完整查询SQL
    SET @full_sql = CONCAT('SELECT `Company name` as 公司名称,', @case_sql, '
    FROM source_credit_table
    WHERE `Month` >= DATE_FORMAT(date_sub(CURRENT_DATE, INTERVAL 11 MONTH), \'%Y-%m\')
    GROUP BY `Company name`');
    -- 执行SQL
    PREPARE stmt FROM @full_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

调用时直接执行CALL get_company_credit_12m();即可拿到最新列结构的查询结果。

注意事项 #

• 源表的Month字段建议统一存储为YYYY-MM格式，避免日期匹配出错
• 如果某公司某个月没有数据，默认返回NULL，可根据需求在CASE语句中加ELSE修改为0或其他默认值
• PostgreSQL、SQL Server等其他数据库仅需调整日期函数和动态SQL语法，整体逻辑通用

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