SQL Server 2019中跨P1ID与P2ID列实现ID去重的需求

解决方案 #

可以使用递归CTE来实现按顺序跟踪已出现的ID，只保留符合规则的记录：

WITH OrderedRecords AS (
    -- 按分组和记录顺序排序，生成行号
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY ID_GROUP ORDER BY ID) AS rn
    FROM InitialResultSet
),
RecursiveCTE AS (
    -- 初始步骤：每个分组的第一条记录直接保留，记录已出现的ID
    SELECT 
        ID,
        ID_GROUP,
        P1ID,
        P2ID,
        P1_RANK,
        P2_RANK,
        CAST(CONCAT(P1ID, ',', P2ID) AS VARCHAR(MAX)) AS seen_ids,
        rn
    FROM OrderedRecords
    WHERE rn = 1

    UNION ALL

    -- 递归处理后续记录
    SELECT 
        ors.ID,
        ors.ID_GROUP,
        ors.P1ID,
        ors.P2ID,
        ors.P1_RANK,
        ors.P2_RANK,
        -- 更新已出现的ID集合
        CASE 
            WHEN CHARINDEX(CONCAT(',', ors.P1ID, ','), CONCAT(',', rcte.seen_ids, ',')) = 0
                 AND CHARINDEX(CONCAT(',', ors.P2ID, ','), CONCAT(',', rcte.seen_ids, ',')) = 0
            THEN CONCAT(rcte.seen_ids, ',', ors.P1ID, ',', ors.P2ID)
            WHEN CHARINDEX(CONCAT(',', ors.P1ID, ','), CONCAT(',', rcte.seen_ids, ',')) = 0
            THEN CONCAT(rcte.seen_ids, ',', ors.P1ID)
            ELSE rcte.seen_ids
        END AS seen_ids,
        ors.rn
    FROM RecursiveCTE rcte
    JOIN OrderedRecords ors 
        ON ors.ID_GROUP = rcte.ID_GROUP 
        AND ors.rn = rcte.rn + 1
    -- 仅保留P1ID未在之前保留记录中出现过的记录
    WHERE CHARINDEX(CONCAT(',', ors.P1ID, ','), CONCAT(',', rcte.seen_ids, ',')) = 0
)
-- 输出最终结果
SELECT ID, ID_GROUP, P1ID, P2ID, P1_RANK, P2_RANK
FROM RecursiveCTE
ORDER BY ID;

逻辑说明 #

1. OrderedRecords：先按ID_GROUP分组，再按ID（记录生成顺序）排序，为每条记录分配行号，确保递归处理的顺序正确。
2. RecursiveCTE初始步骤：每个分组的第一条记录必然保留，同时将该记录的P1ID和P2ID加入已出现ID集合。
3. 递归处理：
   • 对于后续每条记录，先检查P1ID是否已经在之前保留记录的P1ID/P2ID中出现过（用CHARINDEX匹配带前后逗号的字符串，避免部分匹配）。
   • 如果P1ID未出现，再检查P2ID：若P2ID也未出现，则将两个ID都加入已出现集合；若P2ID已出现，仅将P1ID加入集合。
   • 只有P1ID未出现的记录才会被保留，符合规则中“优先按列顺序判断首次出现”的要求。

执行后将得到与DesiredOutcome完全一致的结果。

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