如何通过为重复数据排名并追加排名值实现数据唯一化？

问题背景 #

存在重复记录且无法删除，需要自动为每组重复数据依次追加1、2、3...的序号实现唯一标识；此前使用的依赖行号的脚本因每月数据集变化导致序号失效，需优化原有脚本或提供替代方案。

修改后的可行脚本 #

针对原有脚本的问题，核心是替换不稳定的排序依据，改用不会随数据集变化而打乱顺序的字段（比如主键、记录创建时间等），确保序号生成逻辑稳定：

BEGIN
    ;WITH [DuplicateGroups] AS (
        SELECT 
            -- 按重复分组字段分区，用稳定字段排序生成序号
            ROW_NUMBER() OVER (
                PARTITION BY [重复标识字段] 
                ORDER BY [稳定排序字段] -- 比如主键ID、CreateTime等不会变动的字段
            ) AS [GroupSeq],
            [需要修改的字段],
            [稳定排序字段] -- 需包含该字段用于CTE更新
        FROM [表名]
    )

    UPDATE [DuplicateGroups]
    -- 所有重复项都追加序号（包括第1项，若只需从第2项开始则保留原逻辑）
    SET [需要修改的字段] = CONCAT([需要修改的字段], [GroupSeq])
    -- 若仅需给重复项（排除每组第一个）加序号，保留WHERE [GroupSeq] > 1
    -- WHERE [GroupSeq] > 1

    SELECT [需要修改的字段] FROM [表名] ORDER BY [需要修改的字段]
END

关键优化点 #

• PARTITION BY：必须选择能精准标识重复组的字段（比如原脚本的[COLUMN_NAME]，确保同一组重复数据被分到同一个分区）
• ORDER BY：必须使用稳定且唯一的字段（如主键ID、记录创建时间），避免每月数据新增/调整时，同一组内的序号顺序被打乱
• 更新逻辑：若需要所有重复项都带序号（包括每组第一个），可去掉WHERE [GroupSeq] > 1；若仅需区分重复项（第一个保持原样），则保留该条件

替代方案（不修改原字段） #

如果不想修改原字段的内容，可以新增一个字段存储带序号的唯一值，避免破坏原始数据：

-- 先新增字段
ALTER TABLE [表名] ADD [唯一标识字段] NVARCHAR(255)

-- 填充带序号的唯一值
BEGIN
    ;WITH [DuplicateGroups] AS (
        SELECT 
            ROW_NUMBER() OVER (
                PARTITION BY [重复标识字段] 
                ORDER BY [稳定排序字段]
            ) AS [GroupSeq],
            [唯一标识字段],
            [需要拼接的字段],
            [稳定排序字段]
        FROM [表名]
    )

    UPDATE [DuplicateGroups]
    SET [唯一标识字段] = CONCAT([需要拼接的字段], [GroupSeq])
END

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