如何只保留一行表，删除重复行？ [英] How to keep only one row of a table, removing duplicate rows?

问题描述

我有一个表在名称列中有很多重复。我要
喜欢每个只保留一行。

以下列出了重复项，但我不知道如何删除
重复，只需保留一个：

  SELECT名称FROM成员GROUP BY名称HAVING COUNT（*）> 1; 
  

谢谢。

解决方案

请参阅以下问题：删除重复的行一个表格。

从那里改编的接受的答案（这是我的答案，所以在这里没有盗窃）：

如果您有唯一的ID字段，您可以通过简单的方式执行此操作：您可以删除除ID之外的所有记录，但不具有最小ID为他们的名字。

示例查询：

  DELETE FROM members 
 WHERE ID NOT IN 
（
 SELECT MIN（ID）
 FROM members 
 GROUP BY name 
）
  

如果您没有唯一索引，我的建议是简单添加自动增量唯一索引。主要是因为它的设计很好，也是因为它可以让你运行上面的查询。

I have a table that has a lot of duplicates in the Name column. I'd like to only keep one row for each.

The following lists the duplicates, but I don't know how to delete the duplicates and just keep one:

SELECT name FROM members GROUP BY name HAVING COUNT(*) > 1;

Thank you.

解决方案

See the following question: Deleting duplicate rows from a table.

The adapted accepted answer from there (which is my answer, so no "theft" here...):

You can do it in a simple way assuming you have a unique ID field: you can delete all records that are the same except for the ID, but don't have "the minimum ID" for their name.

Example query:

DELETE FROM members
WHERE ID NOT IN
(
    SELECT MIN(ID)
    FROM members
    GROUP BY name
)

In case you don't have a unique index, my recommendation is to simply add an auto-incremental unique index. Mainly because it's good design, but also because it will allow you to run the query above.

这篇关于如何只保留一行表，删除重复行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！