如何在日期表中基于过去12个月生成时序排序列？

嘿，我来帮你搞定这个需求！你需要给那张包含2018-2021年日期的表新增一列，按照「十月→十一月→十二月→一月→…→九月」的时序给所有记录分配排序值对吧？下面是几种主流数据库的实现方案，你可以根据自己使用的数据库来选：

方案一：直接用CASE WHEN映射（通用所有数据库） #

这是最直观的方式，直接根据MONTH_NAME字段匹配对应的排序值：

1. 持久化新增列（把排序值存到表中） #

-- 第一步：新增列
ALTER TABLE your_date_table ADD COLUMN month_order INT;

-- 第二步：更新排序值
UPDATE your_date_table
SET month_order = CASE MONTH_NAME
    WHEN '十月' THEN 1
    WHEN '十一月' THEN 2
    WHEN '十二月' THEN 3
    WHEN '一月' THEN 4
    WHEN '二月' THEN 5
    WHEN '三月' THEN 6
    WHEN '四月' THEN 7
    WHEN '五月' THEN 8
    WHEN '六月' THEN 9
    WHEN '七月' THEN 10
    WHEN '八月' THEN 11
    WHEN '九月' THEN 12
END;

2. 查询时动态生成（不需要修改表结构） #

如果不需要把排序值永久存在表里，直接在查询时生成即可：

SELECT
    DATE,
    YEAR,
    MONTH,
    MONTH_NAME,
    CASE MONTH_NAME
        WHEN '十月' THEN 1
        WHEN '十一月' THEN 2
        WHEN '十二月' THEN 3
        WHEN '一月' THEN 4
        WHEN '二月' THEN 5
        WHEN '三月' THEN 6
        WHEN '四月' THEN 7
        WHEN '五月' THEN 8
        WHEN '六月' THEN 9
        WHEN '七月' THEN 10
        WHEN '八月' THEN 11
        WHEN '九月' THEN 12
    END AS month_order
FROM your_date_table;

方案二：用映射表关联更新（适合大数据量或规则易变场景） #

如果你的表数据量很大，或者后续可能调整排序规则，推荐先创建一个月份排序映射表，再通过关联更新主表，这样更高效也更易维护：

-- 1. 创建临时映射表（如果是永久规则也可以建普通表）
CREATE TEMPORARY TABLE month_order_map (
    MONTH_NAME VARCHAR(10),
    ORDER_NUM INT
);

-- 2. 插入排序规则数据
INSERT INTO month_order_map VALUES
('十月',1),('十一月',2),('十二月',3),
('一月',4),('二月',5),('三月',6),
('四月',7),('五月',8),('六月',9),
('七月',10),('八月',11),('九月',12);

-- 3. 关联更新主表的排序列
ALTER TABLE your_date_table ADD COLUMN month_order INT;

UPDATE your_date_table d
JOIN month_order_map m ON d.MONTH_NAME = m.MONTH_NAME
SET d.month_order = m.ORDER_NUM;

方案三：数据库专属简化写法（可选） #

针对部分数据库，有更简洁的写法：

PostgreSQL 用array_position #

-- 查询时生成列
SELECT
    DATE,
    YEAR,
    MONTH,
    MONTH_NAME,
    array_position(
        ARRAY['十月','十一月','十二月','一月','二月','三月','四月','五月','六月','七月','八月','九月'],
        MONTH_NAME
    ) AS month_order
FROM your_date_table;

SQL Server 用CHARINDEX（注意中文长度适配） #

-- 查询时生成列
SELECT
    DATE,
    YEAR,
    MONTH,
    MONTH_NAME,
    CHARINDEX(MONTH_NAME, '十月,十一月,十二月,一月,二月,三月,四月,五月,六月,七月,八月,九月') / 4 + 1 AS month_order
FROM your_date_table;
-- 注：这里的除数4是根据最长月份名称（3个中文字符+逗号）的长度调整的，用CASE写法会更稳妥

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