SQL同表不同类别去重保留ID首次所属类别值的优化方案求解

现有一张包含D_id、D_category两个字段的表，原始数据如下：

D_id | D_category
-----------------
   1  |   A
   2  |   A
   3  |   A

   1  |   B
   2  |   B
   4  |   B
   5  |   B

   1  |   C
   2  |   C
   4  |   C
   5  |   C
   6  |   C

去重规则 #

• 类别A中出现过的D_id不允许出现在类别B、C中
• 类别B中出现过的D_id不允许出现在类别C中
• 若新增更多类别，均遵循「优先级靠前的类别中已存在的D_id不得出现在所有后续类别中」的规则

预期输出 #

D_id | D_category
-----------------
   1  |   A
   2  |   A
   3  |   A

   4  |   B
   5  |   B

   6  |   C

现有方案问题 #

当前实现的方案可运行，但无法适配类别数量动态新增的场景，每新增类别都需要手动补充删除逻辑，通用性不足。现有代码如下：

DECLARE @A TABLE(
    D_id INT NOT NULL,
    D_category VARCHAR(MAX));

INSERT INTO @A(D_id,D_category)
VALUES (1, 'A'),
(2, 'A'),
(3, 'A'),
(1, 'B'),
(2, 'B'),
(4, 'B'),
(5, 'B'),
(1, 'C'),
(2, 'C'),
(4, 'C'),
(5, 'C'),
(6, 'C')

DELETE t
FROM @A t
WHERE t.D_category = 'B' AND  EXISTS (SELECT 1 FROM @A t2 WHERE t2.D_category = 'A' and t.D_id = t2.D_id)

DELETE t
FROM @A t
WHERE t.D_category = 'C' AND  EXISTS (SELECT 1 FROM @A t2 WHERE t2.D_category = 'B' and t.D_id = t2.D_id)

DELETE t
FROM @A t
WHERE t.D_category = 'C' AND  EXISTS (SELECT 1 FROM @A t2 WHERE t2.D_category = 'A' and t.D_id = t2.D_id)

select * from @A

核心思路是给每个类别分配优先级，再为每个D_id匹配优先级最高的所属类别，只保留对应记录即可，无需为新增类别单独写删除逻辑。
如果类别优先级与字典序一致（A<B<C<...），直接使用以下SQL即可：

WITH ranked_data AS (
    SELECT 
        D_id,
        D_category,
        -- 按D_id分组，每组内按类别优先级升序排序，取第一条
        ROW_NUMBER() OVER(PARTITION BY D_id ORDER BY D_category ASC) AS rn
    FROM @A
)
SELECT D_id, D_category
FROM ranked_data
WHERE rn = 1
ORDER BY D_category, D_id

如果类别优先级和字典序不匹配，可以单独定义优先级映射表，新增类别时只需在映射表中补充对应优先级即可：

WITH category_priority AS (
    -- 自定义类别优先级，数值越小优先级越高，新增类别只需在此处新增行
    SELECT 'A' AS D_category, 1 AS priority UNION ALL
    SELECT 'B' AS D_category, 2 AS priority UNION ALL
    SELECT 'C' AS D_category, 3 AS priority
),
ranked_data AS (
    SELECT 
        t.D_id,
        t.D_category,
        ROW_NUMBER() OVER(PARTITION BY t.D_id ORDER BY cp.priority ASC) AS rn
    FROM @A t
    JOIN category_priority cp ON t.D_category = cp.D_category
)
SELECT D_id, D_category
FROM ranked_data
WHERE rn = 1
ORDER BY cp.priority, D_id

两种方案都可以完美适配类别动态新增的场景，执行结果与预期完全一致。

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