基于外键列更新行索引的SQL脚本需求

需求：现有数据表中RecordNo列值均为NULL，需要按DeptId分组，为每组内的行填充从1开始递增的索引值（示例逻辑为同部门内按Id排序生成序号）。

不同数据库的实现脚本 #

MySQL 8.0+ 版本

WITH ranked_data AS (
    SELECT 
        Id,
        ROW_NUMBER() OVER (PARTITION BY DeptId ORDER BY Id) AS new_RecordNo
    FROM your_table_name
)
UPDATE your_table_name t
JOIN ranked_data rd ON t.Id = rd.Id
SET t.RecordNo = rd.new_RecordNo;

SQL Server 版本

WITH ranked_data AS (
    SELECT 
        Id,
        ROW_NUMBER() OVER (PARTITION BY DeptId ORDER BY Id) AS new_RecordNo
    FROM your_table_name
)
UPDATE ranked_data
SET RecordNo = new_RecordNo;

PostgreSQL 版本

UPDATE your_table_name t
SET RecordNo = rd.new_RecordNo
FROM (
    SELECT 
        Id,
        ROW_NUMBER() OVER (PARTITION BY DeptId ORDER BY Id) AS new_RecordNo
    FROM your_table_name
) rd
WHERE t.Id = rd.Id;

注意事项 #

• 将脚本中的your_table_name替换为实际的数据表名称
• ORDER BY Id可根据业务需求替换为其他排序字段（如创建时间、业务编号等），确保每组内的序号顺序符合预期

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