如何更新MySQL表，为同名称重复记录添加rank_dupe排名序号？

核心思路是通过唯一的id字段，将你生成的排名数据与原表关联，从而完成rank_dupe字段的更新。以下是不同主流数据库的具体实现方案：

MySQL（8.0+ 版本） #

使用CTE（公共表表达式）关联更新：

WITH ranked_data AS (
    SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS row_num
    FROM myTable
)
UPDATE myTable t
JOIN ranked_data rd ON t.id = rd.id
SET t.rank_dupe = rd.row_num;

如果是MySQL 5.x 不支持CTE，改用子查询关联：

UPDATE myTable t
JOIN (
    SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS row_num
    FROM myTable
) rd ON t.id = rd.id
SET t.rank_dupe = rd.row_num;

PostgreSQL #

借助CTE配合UPDATE语句：

WITH ranked_data AS (
    SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS row_num
    FROM myTable
)
UPDATE myTable t
SET rank_dupe = rd.row_num
FROM ranked_data rd
WHERE t.id = rd.id;

SQL Server #

直接在CTE中包含需要更新的字段，然后执行更新：

WITH ranked_data AS (
    SELECT
        id,
        rank_dupe,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS row_num
    FROM myTable
)
UPDATE ranked_data
SET rank_dupe = row_num;

注意事项 #

• 确保id字段是表中的唯一标识，避免关联时出现匹配错误。
• 执行更新前建议先通过SELECT语句验证关联结果是否符合预期，必要时备份数据。

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