如何在SQL中生成按月重置的序列编号格式？

要实现你需要的格式，核心是利用**窗口函数ROW_NUMBER()**按年月分组生成自增序号，当月度变化时自动重置为1。以下是针对不同场景的实现方案：

场景1：基于现有数据表的记录 #

假设你的数据表（your_table）中有一个存储记录日期的字段（比如record_date），可以用以下SQL：

SELECT 
    CONCAT(
        'D/', 
        FORMAT(record_date, 'MM-yy'), 
        '/', 
        ROW_NUMBER() OVER (
            PARTITION BY FORMAT(record_date, 'MM-yy') 
            ORDER BY record_date -- 可替换为主键或其他排序字段控制序号顺序
        )
    ) AS formatted_sequence
FROM 
    your_table;

关键部分解释： #

• PARTITION BY FORMAT(record_date, 'MM-yy')：按年月分组，每个分组内的序号从1重新开始计数
• ORDER BY record_date：指定分组内序号的递增顺序，确保序号按时间（或你需要的逻辑）有序生成
• 最后通过CONCAT把前缀、年月格式和序号拼接成目标格式

场景2：生成测试用序列（无现有表） #

如果只是需要生成示例中的测试序列，可以用CTE生成模拟日期数据，再套用同样的逻辑：

WITH simulated_dates AS (
    -- 生成2022年11月到12月的模拟日期记录
    SELECT CAST('2022-11-01' AS DATE) AS record_date
    UNION ALL
    SELECT DATEADD(DAY, 1, record_date)
    FROM simulated_dates
    WHERE record_date < '2022-12-03'
)
SELECT 
    CONCAT(
        'D/', 
        FORMAT(record_date, 'MM-yy'), 
        '/', 
        ROW_NUMBER() OVER (
            PARTITION BY FORMAT(record_date, 'MM-yy') 
            ORDER BY record_date
        )
    ) AS formatted_sequence
FROM simulated_dates
ORDER BY record_date;

注意事项： #

• 以上代码基于SQL Server（FORMAT和DATEADD是SQL Server语法），如果使用其他数据库：
  • MySQL 8.0+：替换FORMAT(record_date, 'MM-yy')为DATE_FORMAT(record_date, '%m-%y')，窗口函数用法一致
  • PostgreSQL：替换FORMAT(record_date, 'MM-yy')为TO_CHAR(record_date, 'MM-YY')，窗口函数用法一致
• 如果没有明确的日期字段，也可以用记录的创建时间或主键来分组排序，确保序号唯一且符合业务逻辑

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