SQL中按id分组遍历列将重复值替换为不同名称的方法

不需要写循环，用窗口函数就能高效实现需求。核心思路是按id和dept分组，给每组内的重复行生成序号，再将序号拼接在原部门值后；不重复的部门值保持原样。

1. 查询生成目标结果（不修改原表） #

以下是主流数据库的实现代码：

MySQL 版本 #

SELECT 
    id,
    CASE 
        -- 仅当该id下的部门存在重复时，拼接序号
        WHEN COUNT(*) OVER (PARTITION BY id, dept) > 1 
        THEN CONCAT(dept, ROW_NUMBER() OVER (PARTITION BY id, dept ORDER BY (SELECT NULL)))
        ELSE dept
    END AS dept
FROM your_table;

PostgreSQL/SQL Server 版本 #

SELECT 
    id,
    CASE 
        WHEN COUNT(*) OVER (PARTITION BY id, dept) > 1 
        THEN CONCAT(dept, ROW_NUMBER() OVER (PARTITION BY id, dept ORDER BY (SELECT 1)))
        ELSE dept
    END AS dept
FROM your_table;

说明：

• PARTITION BY id, dept：按用户id和部门分组，确保序号只在同一id的同一部门内生成
• ROW_NUMBER()：给每组内的行生成连续序号
• ORDER BY (SELECT NULL)/(SELECT 1)：如果不需要指定排序规则，用这种方式跳过排序（不同数据库语法略有差异）

2. 直接更新原表 #

如果需要直接修改原表数据，以MySQL为例（其他数据库逻辑类似）：

UPDATE your_table t1
JOIN (
    SELECT 
        -- 建议用表的唯一主键（比如row_id）关联，避免更新错误
        row_id,
        dept,
        ROW_NUMBER() OVER (PARTITION BY id, dept ORDER BY (SELECT NULL)) AS rn,
        COUNT(*) OVER (PARTITION BY id, dept) AS cnt
    FROM your_table
) t2 ON t1.row_id = t2.row_id
SET t1.dept = CONCAT(t2.dept, t2.rn)
WHERE t2.cnt > 1;

注意：如果表没有唯一主键，需确保id+dept+其他字段能唯一标识行，避免重复更新同一行。

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