如何在SQL中删除重复记录

Question

可能重复：
如何使用SQL删除重复行？

我有一个记录表，想要删除所有重复的记录。

DELETE FROM 'table'
WHERE 'field' IN 
(
SELECT 'field' FROM 'table' GROUP BY 'field'
HAVING (COUNT('field')>1)
)

为什么这个不起作用？

- vdhmartijn

---

你的查询不会删除所有字段，包括重复的吗？如果你有两个具有相同ID的重复记录，你的IN()查询将会删除它们。难道你不想至少保留一条记录吗？ - GGio

MySQL不允许你在从中选择数据的表上进行更新/删除操作。 - Marc B

你能添加示例记录吗？你有自增列吗？ - John Woo

这是由于MySQL的愚蠢限制引起的。请看我的回答。 - user330315

完成后（无论哪个答案有效），请确保添加“UNIQUE”约束，以便将来不必再次执行此操作。 - Damien_The_Unbeliever

Answer 1

也许你可以使用命令 DISTINCT 来选择基于某个字段的唯一记录。

你可以创建一个以唯一条目为基础的新表。 例如...

CREATE TABLE nonDuplicates  
SELECT DISTINCT * FROM yourTable group by field

- peixe

---

这只能工作是因为MySQL对GROUP BY的处理基本上是不正确的，从而允许这种无效的语法。但这很可能是最快的解决方案。 - user330315

@vdhmartijn：请阅读这篇文章 http://rpbouman.blogspot.de/2007/05/debunking-group-by-myths.html，以便了解为什么MySQL可以工作而其他数据库管理系统将拒绝该语句作为无效。 - user330315

Answer 2

这会给你多个结果：-

SELECT field FROM `table`
GROUP BY field
HAVING (COUNT('field)>1

尝试通过以下方式进行更改：

SELECT TOP 1 field
FROM `table`
GROUP BY field 
HAVING (COUNT(field)>1

- Andrew Adamich

---

SELECT TOP 1 对我不起作用.. 仍然无法工作。 - vdhmartijn

这会删除什么东西？ - user330315

Answer 3

首先获取所有重复项并将它们存储在数组中。

SELECT field 
FROM `table`
GROUP BY field
HAVING COUNT('field') > 1

现在你可以编写PHP代码，保存任何你想要的结果并执行它。

DELETE 
FROM `table`
WHERE field IN (your values) AND field != savedID

- GGio

---

谢谢您的回复，但我的意图是只使用SQL而不使用PHP来工作。 - vdhmartijn

Answer 4

MySQL有一个非常严格的限制，即不能在子查询中使用正在更新/删除/插入的表。

但是，您可以通过将要删除的表连接起来（而不是使用子查询）来解决这个问题。

假设您的表中有某种唯一标识符（我假设以下语句中有一个名为id的列）：

DELETE d 
FROM table_with_duplicates d 
JOIN ( 
   SELECT min(id) as min_id, field
   FROM table_with_duplicates 
   GROUP BY field
) keep ON keep.field = d.field
      AND keep.min_id <> d.id; 

这将保留每个重复记录的一行（其中id列中具有最低值的行）。

如果您想要删除所有重复行（而不是至少保留一行），只需删除AND keep.min_id <> d.id条件即可。

编辑

如果您没有唯一的列，但想要删除所有重复项（不保留至少一行记录），则可以使用如下：

DELETE d 
FROM table_with_duplicates d 
JOIN ( 
   SELECT field
   FROM table_with_duplicates 
   GROUP BY field
   HAVING count(*) > 1
) del ON del.field = d.field;

- user330315

---

如果我没有ID，还有解决的办法吗？ - vdhmartijn

如果您想删除所有重复项（而不保留至少一个），则可以不使用ID。否则请参见peixe的答案。 - user330315