PostgreSQL如何按id分组删除整组完全重复的行

测试表包含id、name（姓名）、subject（科目）、score（分数）4个字段，测试数据如下：

• id=1：Ann的Maths（数学）9分、History（历史）8分，共2条记录
• id=2：Ann的Maths 9分、History 8分，共2条记录
• id=3：Ann的Maths 9分、History 7分，共2条记录
• id=4：Bob的Maths 8分、History 8分，共2条记录

需求规则：

• 仅当两个不同id对应的所有行，除id字段外完全一一匹配时，删除id值更大的分组下的全部行
• 只要两个分组存在任意一行不匹配，就不能删除任何一个分组的内容

预期处理结果：删除id=2的所有记录，保留id1、id3、id4的全部记录。因为id1和id2的非id字段完全匹配；id1和id3的History科目分数存在差异，不满足全匹配条件；id4无匹配的重复分组，予以保留。

你之前写的SQL是单行维度的去重逻辑：

DELETE FROM  table a
USING table b
WHERE a.name = b.name
AND a.subject = b.subject
AND a.score = b.score
AND a.ID < b.ID;

这个逻辑只会判断单条记录是否重复，不会校验整个id分组下的所有行是否完全匹配，会误删id=3下和id=1重复的Maths行，不符合整组判断的要求。

核心思路是先做整组一致性校验，只有两个id分组的非id字段集合完全相等时，才标记更大的id为待删除目标，再做整组删除。

方案1：集合对比法（逻辑严谨，推荐） #

通过双向差集判断两个分组的行集合是否完全一致，支持PostgreSQL、MySQL 8.0+等支持CTE和EXCEPT语法的数据库：

WITH duplicate_ids AS (
    SELECT DISTINCT b.id AS del_id
    FROM test_table a
    JOIN test_table b 
      ON a.id < b.id 
     -- 校验a组所有行都在b组存在
     AND NOT EXISTS (
        SELECT a2.name, a2.subject, a2.score 
        FROM test_table a2 
        WHERE a2.id = a.id
        EXCEPT
        SELECT b2.name, b2.subject, b2.score 
        FROM test_table b2 
        WHERE b2.id = b.id
     )
     -- 校验b组所有行都在a组存在
     AND NOT EXISTS (
        SELECT b3.name, b3.subject, b3.score 
        FROM test_table b3 
        WHERE b3.id = b.id
        EXCEPT
        SELECT a3.name, a3.subject, a3.score 
        FROM test_table a3 
        WHERE a3.id = a.id
     )
)
DELETE FROM test_table
WHERE id IN (SELECT del_id FROM duplicate_ids);

针对测试数据运行时，只会标记id=2为待删除id，完全符合预期。

方案2：分组签名法（兼容更多数据库版本） #

如果数据库不支持EXCEPT语法，可以给每个id分组生成唯一的内容签名，签名一致则代表分组内容完全匹配，适用于支持GROUP_CONCAT/STRING_AGG聚合函数的数据库：

WITH group_signature AS (
    SELECT 
        id,
        -- 按固定顺序拼接组内所有非id字段生成唯一标识，分隔符选择字段值不会出现的特殊字符即可
        GROUP_CONCAT(
            CONCAT(name,'|',subject,'|',score) 
            ORDER BY name, subject, score 
            SEPARATOR ';;'
        ) AS sig
    FROM test_table
    GROUP BY id
),
duplicate_ids AS (
    SELECT b.id AS del_id
    FROM group_signature a
    JOIN group_signature b 
      ON a.id < b.id 
     AND a.sig = b.sig
)
DELETE FROM test_table WHERE id IN (SELECT del_id FROM duplicate_ids);

注意：使用该方法时要选择不会出现在name、subject字段值中的分隔符，避免因字段内容和分隔符冲突导致签名匹配错误，优先选择集合对比法。

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