MySQL中如何更新重复ARCustomerCode字段并追加唯一行ID

要给重复的ARCustomerCode追加对应的唯一行ID，你可以通过窗口函数分组编号后执行更新，具体操作如下：

1. 先给重复数据分配组内序号 #

使用ROW_NUMBER()窗口函数，按ARCustomerCode分组，为每组内的行生成递增序号，确认更新逻辑：

SELECT 
    ARCustomerCode,
    唯一行ID字段名, -- 替换为你表中实际的唯一ID字段（如id）
    ROW_NUMBER() OVER (PARTITION BY ARCustomerCode ORDER BY 唯一行ID字段名) AS row_num
FROM customers
WHERE ARCustomerCode IN (
    SELECT ARCustomerCode
    FROM customers
    GROUP BY ARCustomerCode
    HAVING COUNT(ARCustomerCode) > 1
);

ORDER BY子句用于保证序号分配的稳定性，避免每次查询序号不一致。

2. 执行更新操作 #

根据分配的序号，将原ARCustomerCode与序号拼接后更新字段：

适用于PostgreSQL、SQL Server等支持CTE的数据库 #

WITH ranked_customers AS (
    SELECT 
        ARCustomerCode,
        唯一行ID字段名,
        ROW_NUMBER() OVER (PARTITION BY ARCustomerCode ORDER BY 唯一行ID字段名) AS row_num
    FROM customers
    WHERE ARCustomerCode IN (
        SELECT ARCustomerCode
        FROM customers
        GROUP BY ARCustomerCode
        HAVING COUNT(ARCustomerCode) > 1
    )
)
UPDATE customers
SET ARCustomerCode = CONCAT(ranked_customers.ARCustomerCode, '-', ranked_customers.row_num)
FROM ranked_customers
WHERE customers.唯一行ID字段名 = ranked_customers.唯一行ID字段名;

适用于MySQL的写法 #

UPDATE customers
JOIN (
    SELECT 
        ARCustomerCode,
        唯一行ID字段名,
        ROW_NUMBER() OVER (PARTITION BY ARCustomerCode ORDER BY 唯一行ID字段名) AS row_num
    FROM customers
    WHERE ARCustomerCode IN (
        SELECT ARCustomerCode
        FROM customers
        GROUP BY ARCustomerCode
        HAVING COUNT(ARCustomerCode) > 1
    )
) AS ranked_customers
ON customers.唯一行ID字段名 = ranked_customers.唯一行ID字段名
SET customers.ARCustomerCode = CONCAT(ranked_customers.ARCustomerCode, '-', ranked_customers.row_num);

关键注意事项 #

• 操作前务必备份数据，防止误操作导致数据丢失。
• 若不想修改原ARCustomerCode字段，可新增一个字段（如NewARCustomerCode）来存储拼接后的值，避免破坏原始数据。

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