如何删除重复行并保留第一行？

Question

我犯了一个错误，我有不想要的重复内容。

我有一张表格，其中包含4个关键字段：A1、k1、k2和k3。

A1是自动递增和主键。

k1、k2和k3的组合应该是唯一的，我必须在创建唯一索引之前删除重复行。有些行有一个重复项，有些行有多个。

SELECT CONCAT(k1, k2, k) AS dup_value
  FROM myviews
 GROUP BY dup_value
HAVING (COUNT(dup_value) > 1)

这显示了我需要处理的重复值。但是现在我不知道如何保留一个并删除每个重复集的其余部分。

- sdfor

---

有很多关于这个问题的问题。应该快速将其关闭为重复项。搜索“删除重复行”。这是我找到的第一个结果。 - rockerest

@rockerest有趣的是我先尝试了你提供的解决方案，但在MySQL中并没有起作用。OMG Ponies的答案对我在MySQL中有效，所以我的+1就给了他。 - jsleuth

Answer 1

备份数据，然后...

MySQL支持在DELETE语句中使用JOIN。如果您想保留重复项中的第一个：

DELETE a
  FROM MYVIEWS a
  JOIN (SELECT MIN(t.a1) AS min_a1, t.k1, t.k2, t.k3
          FROM MYVIEWS t
      GROUP BY t.k1, t.k2, t.k3
        HAVING COUNT(*) > 1) b ON b.k1 = a.k1
                              AND b.k2 = a.k2
                              AND b.k3 = a.k3
                              AND b.min_a1 != a.a1

如果您想保留重复项中的最后一个：

DELETE a
  FROM MYVIEWS a
  JOIN (SELECT MAX(t.a1) AS max_a1, t.k1, t.k2, t.k3
          FROM MYVIEWS t
      GROUP BY t.k1, t.k2, t.k3
        HAVING COUNT(*) > 1) b ON b.k1 = a.k1
                              AND b.k2 = a.k2
                              AND b.k3 = a.k3
                              AND b.max_a1 != a.a1

- OMG Ponies

---

我遇到了一个错误 #1093 - 无法在FROM子句中指定目标表'myviews'进行更新。 - sdfor

@sdfor：抱歉，忘记了那个限制——请尝试更新后的代码。 - OMG Ponies

Answer 2

像这样的东西吗？

DELETE FROM myviews WHERE EXISTS(SELECT CONCAT(k1, k2, k) AS dup_value
FROM myviews
GROUP BY dup_value
HAVING (COUNT(dup_value) > 1));

- Automatico

---

或许我误解了你的问题。你是指一个元组内部的重复吗？ - Automatico

这是错误的（至少相对于问题而言），因为它会删除重复集合中的所有行。 - Saic Siquot

除了删除所有行而不是除了一个之外（如@LuisSiquot所提到的），当在表上运行时，这将导致错误：SQL错误（1093）：您无法在FROM子句中指定目标表'mytable'进行更新。 - ebyrob

Answer 3

您可以创建一个结构相同但为空的新表，然后在其上创建唯一键，然后使用INSERT IGNORE/SELECT * FROM将原始表插入到新表中，最后删除原始表。

INSERT IGNORE会自动忽略任何主键或唯一键问题，并跳过重复项。

- spanky

---

不是生产系统的理想做法--查询将依赖于旧表，数据库中不能有同名的多个表。这将需要停机时间。 - OMG Ponies

非常正确。我假设 OP 的项目还没有投入生产，因为它在更改键和删除大量行，但是我不应该做出这样的假设！ - spanky

Answer 4

你的concat函数需要一个分隔符，否则"a"、"b"和"cd"就会被视为"abcd"、""和""相同。

- Nick ODell