SQL Server中按外键ID分组递增更新displayorder字段

嘿，别担心，这种按分组给字段递增值的需求真的很常见，哪怕不太精通SQL也能轻松搞定！

你想要的效果是按FKID分组，每组内的displayorder从1开始依次递增——这时候窗口函数ROW_NUMBER()就是你的绝佳工具，几乎所有主流数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle等）都支持它。

第一步：先验证结果是否符合预期 #

你可以先执行查询语句，确认生成的编号是你想要的：

SELECT 
    FKID,
    ROW_NUMBER() OVER (PARTITION BY FKID ORDER BY id) AS displayorder
FROM 
    your_table_name;

这里的关键参数：

• PARTITION BY FKID：指定按FKID字段分组，每组独立编号
• ORDER BY id：确定每组内的行排序依据（这里用你的主键id来保证顺序稳定，你也可以换成其他业务相关的字段，比如创建时间）
• ROW_NUMBER()：自动给每组内的行从1开始生成连续编号

第二步：更新表中的displayorder字段 #

如果验证没问题，就可以把新编号更新到原表中了，不同数据库的写法略有差异：

MySQL 写法

UPDATE your_table_name t
JOIN (
    SELECT 
        id,
        ROW_NUMBER() OVER (PARTITION BY FKID ORDER BY id) AS new_displayorder
    FROM your_table_name
) temp ON t.id = temp.id
SET t.displayorder = temp.new_displayorder;

PostgreSQL/SQL Server 写法

可以用CTE（公共表达式）来更清晰地完成更新：

WITH temp AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (PARTITION BY FKID ORDER BY id) AS new_displayorder
    FROM your_table_name
)
UPDATE your_table_name t
SET displayorder = temp.new_displayorder
FROM temp
WHERE t.id = temp.id;

小提醒 #

一定要注意ORDER BY后面的字段哦，它直接决定了每组内的行是按什么顺序来编号的。比如你希望按数据插入的先后顺序编号，就用主键id；如果有业务上的排序要求，就换成对应的字段（比如create_time），不然编号顺序可能会不符合你的预期~

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