SQL Server 2014重复数据处理求助：按规则合并记录并标记

需求与规则明确 #

需要处理SQL Server 2014（v12.0）导入数据表中的重复数据，最终实现每个eCRFNo仅保留一条记录，遵循以下规则：

• 当eCRFNo和NotesURL同时重复时：按eCRFNo→NotesURL→Assignee排序，保留第一条记录，将同组其他记录的Assignee合并到新列Watcher（分号分隔）。
• 当eCRFNo重复但NotesURL不重复时：仅保留排序后的第一条记录，并在新列Label中添加Replication标记。
• 允许直接删除不需要保留的重复记录。

修正后的实现代码 #

原代码的分区逻辑和处理方式未完全匹配需求，以下是符合规则的完整实现：

WITH cte AS (
    SELECT 
        *,
        -- 按eCRFNo+NotesURL分组，排序后标记组内序号
        ROW_NUMBER() OVER (PARTITION BY eCRFNo, NotesURL ORDER BY eCRFNo, NotesURL, Assignee) AS rn_url_group,
        -- 按eCRFNo分组，排序后标记全局序号（用于筛选唯一保留记录）
        ROW_NUMBER() OVER (PARTITION BY eCRFNo ORDER BY eCRFNo, NotesURL, Assignee) AS rn_crf_group,
        -- 统计同eCRFNo下不同NotesURL的数量，判断是否触发Replication标记
        COUNT(DISTINCT NotesURL) OVER (PARTITION BY eCRFNo) AS distinct_url_count
    FROM Matched
),
watcher_cte AS (
    -- 生成同eCRFNo+NotesURL组内的Assignee合并字符串
    SELECT 
        eCRFNo,
        NotesURL,
        STUFF((
            SELECT ';' + Assignee
            FROM cte c2
            WHERE c2.eCRFNo = c1.eCRFNo AND c2.NotesURL = c1.NotesURL AND c2.rn_url_group > 1
            FOR XML PATH(''), TYPE
        ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS Watcher
    FROM cte c1
    WHERE rn_url_group = 1
)
-- 第一步：更新需要保留的主记录的Watcher和Label字段
UPDATE m
SET 
    m.Watcher = wc.Watcher,
    m.Label = CASE WHEN c.distinct_url_count > 1 THEN 'Replication' ELSE NULL END
FROM Matched m
JOIN cte c ON m.UID = c.UID
LEFT JOIN watcher_cte wc ON c.eCRFNo = wc.eCRFNo AND c.NotesURL = wc.NotesURL
WHERE c.rn_crf_group = 1;

-- 第二步：删除所有非主记录，确保每个eCRFNo仅存一条
DELETE FROM Matched
WHERE UID IN (
    SELECT UID
    FROM cte
    WHERE rn_crf_group > 1
);

代码逻辑说明 #

1. 分组序号标记：

   • rn_url_group：用于识别同eCRFNo+NotesURL组内的主记录（序号=1），后续合并该组其他Assignee到主记录的Watcher。
   • rn_crf_group：用于识别整个eCRFNo组内的唯一保留记录（序号=1），其余记录将被删除。
   • distinct_url_count：判断同eCRFNo下是否存在多个不同的NotesURL，以此决定是否添加Replication标记。

2. Watcher字段生成：
   使用STUFF结合FOR XML PATH的方式，将同组内的其他Assignee拼接为分号分隔的字符串，通过TYPE.value处理避免XML转义问题。

3. 更新与删除操作：

   • 先更新主记录的Watcher和Label字段，确保保留的记录包含所需的合并信息和标记。
   • 再删除所有rn_crf_group>1的重复记录，最终实现每个eCRFNo仅保留一条记录的目标。

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