删除具有重复主复合键的行

Question

我有一个由以下整数列组成的表:

group_id, person_id, sequence

理想情况下，主键应该是(group_id, person_id)，但是我需要先删除一些重复的键。当存在重复时，我只想保留最低的序列值。

我想出了一个查询来选择一些错误的行，但它只得到重复集中最高的序列号。

SELECT COUNT(*) AS num, group_id, person_id, MAX(sequence)
FROM my_table
GROUP BY group_id, person_id
HAVING COUNT(*) > 1;

我肯定我错过了一些简单的东西。有没有简单的方法可以删除这些重复的内容?

谢谢。

Answer 1

尝试编写一个返回要删除的行的查询。假设(group_id,person_id,sequence)的组合是唯一的，并且您没有NULL值…

 SELECT t.* 
   FROM my_table t
   JOIN ( SELECT o.group_id
               , o.person_id
               , MAX(o.sequence) AS max_sequence
            FROM my_table o
           GROUP BY o.group_id, o.person_id
          HAVING COUNT(*) > 1
        ) d
    ON d.group_id      = t.group_id
   AND d.person_id     = t.person_id
   AND d.max_sequence  = t.sequence

我们可以将SELECT关键字替换为DELETE关键字，将其转换为DELETE语句。

或者，当我用类似的语句删除行时，我通常会创建一个表作为我要删除的行的"备份"。

在SELECT前加CREATE TABLE some_new_table_name AS。

然后，我们可以在DELETE查询中引用"保存"的行

DELETE t.*
  FROM my_table t
  JOIN some_new_table_name d
    ON d.group_id      = t.group_id
   AND d.person_id     = t.person_id
   AND d.max_sequence  = t.sequence

---

此方法只获得重复项中的"一个"。如果原始查询的count值大于2，那么我们需要重复此操作足够多次，每次删除最大的序列值，直到没有计数值大于1为止。

如果有很多重复要删除，我们可以使用稍微不同的模式来一次性删除它们。

不是返回MAX(sequence)(我们想要删除的行)，而是返回MIN(sequence)，我们想要保留的行。我们会改变谓词

    AND d.max_sequence  = t.sequence

to be

    AND d.min_sequence  <> t.sequence

因此，我们删除所有行group_id, person_id除了一个最小值。

我强烈建议您在将其转换为DELETE语句之前先将其编写为SELECT语句。我还建议您对表进行良好的备份和/或"保存"要删除的行的副本。以防万一需要恢复某些行

Answer 2

所有列必须重复。将所有列像这样分组

select * from my_table where not EXISTS (
    SELECT group_id, person_id, min(sequence)
    FROM my_table
    GROUP BY group_id, person_id
    HAVING COUNT(*) > 1);