重分配重复记录引用并删除冗余记录的技术实现需求

重复记录判定规则 #

仅当表1中以下字段全部信息完全一致时，判定为重复记录：

• ItemRef
• Age
• Life
• Condition
• Comments
• HS
• Picturefile
• Immediate
• DETAILS_REF

表1的唯一标识符为PDS_Item_Ref，表中包含其他无关列。

原始数据 #

表1（含重复记录） #

表2（关联表） #

表2通过PDS_Item_Ref字段关联表1，部分/全部重复记录可能被引用：

处理步骤及SQL实现 #

目标：将表2中重复记录的关联值更新为对应组内最小的PDS_Item_Ref，再删除表1的冗余记录（保留每组最小PDS_Item_Ref的记录）。

步骤1：更新表2的关联字段 #

将表2中指向重复记录的PDS_Item_Ref替换为对应组的最小ID：

-- 生成每个重复组的最小PDS_Item_Ref映射
WITH DuplicateGroups AS (
    SELECT 
        ItemRef, Age, Life, Condition, Comments, HS, Picturefile, Immediate, DETAILS_REF,
        MIN(PDS_Item_Ref) AS Min_PDS_Item_Ref
    FROM 表1
    GROUP BY ItemRef, Age, Life, Condition, Comments, HS, Picturefile, Immediate, DETAILS_REF
    HAVING COUNT(*) > 1
)
-- 更新表2关联字段
UPDATE t2
SET t2.PDS_Item_Ref = dg.Min_PDS_Item_Ref
FROM 表2 t2
JOIN 表1 t1 ON t2.PDS_Item_Ref = t1.PDS_Item_Ref
JOIN DuplicateGroups dg ON 
    t1.ItemRef = dg.ItemRef
    AND t1.Age = dg.Age
    AND t1.Life = dg.Life
    AND t1.Condition = dg.Condition
    AND t1.Comments = dg.Comments
    AND t1.HS = dg.HS
    AND ISNULL(t1.Picturefile, '') = ISNULL(dg.Picturefile, '') -- 兼容NULL值匹配
    AND t1.Immediate = dg.Immediate
    AND t1.DETAILS_REF = dg.DETAILS_REF
WHERE t2.PDS_Item_Ref != dg.Min_PDS_Item_Ref;

步骤2：删除表1的冗余记录 #

保留每组中PDS_Item_Ref最小的记录，删除其余重复项：

WITH DuplicateRecords AS (
    SELECT 
        PDS_Item_Ref,
        -- 按重复分组排序，最小ID排第1位
        ROW_NUMBER() OVER (
            PARTITION BY ItemRef, Age, Life, Condition, Comments, HS, Picturefile, Immediate, DETAILS_REF
            ORDER BY PDS_Item_Ref ASC
        ) AS RowNum
    FROM 表1
)
DELETE FROM DuplicateRecords
WHERE RowNum > 1;

最终结果验证 #

• 表2中所有原指向1872、1900的记录，PDS_Item_Ref会被更新为1830
• 表1仅保留PDS_Item_Ref为1830的记录，其余重复项被删除

内容的提问来源于stack exchange，提问作者MG SQL Muppet