问题描述
我有一个在名称列中有很多重复项的表.ID喜欢只保留一行.
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;
谢谢.
推荐答案
查看以下问题:删除表中的重复行.
从那里改编后接受的答案(这是我的答案，所以这里没有盗窃"......):
The adapted accepted answer from there (which is my answer, so no "theft" here...):
假设您有一个唯一的 ID 字段，您可以通过简单的方式执行此操作:您可以删除除 ID 之外的所有相同的记录，但它们的名称没有最小 ID".
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.
查询示例:
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.
这篇关于如何只保留表格的一行，删除重复的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！