如何按日期为数据库多行的s_n列添加序列号

以下是主流数据库中，按日期分组为s_n列批量生成递增序列号的代码方案：

MySQL/MariaDB #

利用窗口函数ROW_NUMBER()按日期分组排序，先预览结果再执行更新：

-- 预览生成的序列号
SELECT 
    date_col,
    ROW_NUMBER() OVER (PARTITION BY DATE(date_col) ORDER BY date_col) AS new_s_n
FROM your_table;

-- 确认无误后更新s_n列
UPDATE your_table t
JOIN (
    SELECT 
        id, -- 替换为你的表主键列名
        ROW_NUMBER() OVER (PARTITION BY DATE(date_col) ORDER BY date_col) AS new_s_n
    FROM your_table
) temp ON t.id = temp.id
SET t.s_n = temp.new_s_n;

SQL Server #

使用CTE结合窗口函数实现更新：

-- 预览结果
SELECT 
    date_col,
    ROW_NUMBER() OVER (PARTITION BY CAST(date_col AS DATE) ORDER BY date_col) AS new_s_n
FROM your_table;

-- 执行更新
WITH temp AS (
    SELECT 
        s_n,
        ROW_NUMBER() OVER (PARTITION BY CAST(date_col AS DATE) ORDER BY date_col) AS new_s_n
    FROM your_table
)
UPDATE temp
SET s_n = new_s_n;

PostgreSQL #

通过CTE和窗口函数完成批量更新：

-- 预览序列号
SELECT 
    date_col,
    ROW_NUMBER() OVER (PARTITION BY date_trunc('day', date_col) ORDER BY date_col) AS new_s_n
FROM your_table;

-- 执行更新
WITH temp AS (
    SELECT 
        id, -- 替换为你的表主键列名
        ROW_NUMBER() OVER (PARTITION BY date_trunc('day', date_col) ORDER BY date_col) AS new_s_n
    FROM your_table
)
UPDATE your_table t
SET s_n = temp.new_s_n
FROM temp
WHERE t.id = temp.id;

注意事项 #

• 确保表存在唯一主键（如id），避免更新时出现数据匹配错误
• 若日期列包含时间戳，需用DATE()、CAST(AS DATE)或date_trunc()提取纯日期部分，保证同一天的行被归为一组
• 务必先执行预览语句确认序列号生成符合预期，再执行更新操作
• 如果需要新增数据时自动生成对应日期的序列号，可创建触发器。以MySQL为例：

CREATE TRIGGER auto_generate_s_n
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
    SET NEW.s_n = (
        SELECT COALESCE(MAX(s_n), 0) + 1
        FROM your_table
        WHERE DATE(date_col) = DATE(NEW.date_col)
    );
END;

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