mysql - 删除mysql表中的所有重复项

Question

考虑下表。它是从 CSV 导入的，没有主键。

+-----------+----------+----+----+----+
| firstname | lastname | c1 | c2 | c3 |
+-----------+----------+----+----+----+
| johnny    | bravo    | a  | b  | c  |
| bruce     | willis   | x  | y  | x  |
| john      | doe      | p  | q  | r  |
| johnny    | bravo    | p  | q  | r  |
| johnny    | bravo    | p  | q  | r  |
| bruce     | willis   | x  | y  | z  |
+-----------+----------+----+----+----+

我想删除表中 (firstname, lastname) 出现不止一次的所有行。所以输出将是:

+-----------+----------+----+----+----+
| firstname | lastname | c1 | c2 | c3 |
+-----------+----------+----+----+----+
| john      | doe      | p  | q  | r  |
+-----------+----------+----+----+----+

Answer 1

在 MySQL 中，最好的方法是使用 join:

delete t
from t join
(
    select t2.firstname, t2.lastname
    from t t2
    group by t2.firstname, t2.lastname
    having count(*) > 1
) t2
    on t.firstname = t2.firstname and
       t.lastname = t2.lastname;