如何删除mySQL重复项 [英] How to remove mySQL duplicate entries

问题描述

我有一个包含重复条目的mySQL表(也许在某些情况下是多个重复项).我有一个名为id的列，它可能包含重复的ID，还有一个称为唯一ID的列，顾名思义，它包含唯一的ID.使用以下SQL语句，我可以选择重复的行

I have a mySQL table with duplicate entries (perhaps in some cases multiple duplicates). I have column called id, which may contain duplicate ids and one called unique id which, as the name suggest contains unique ids. Using the following SQL statement I am able to select the duplicate row

SELECT id,
COUNT(id) AS NumOccurrences
FROM `TABLE 3`
GROUP BY id
HAVING ( COUNT(id) > 1 )

但是我该如何删除(除其中一个以外的所有)?

but how do I delete (all but one of) them?

推荐答案

DELETE t1 FROM test t1, test t2 WHERE t1.unique_id > t2.unique_id AND t1.id = t2.id;

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