如何只保留表格的一行，删除重复的行?

2022-01-10 00:00:00 sql duplicates sqlite

我有一个在名称列中有很多重复项的表.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.

相关文章