mysql - 删除 mySQL 5.7.9 中的所有重复行？

Question

我想使用 GROUP BY 删除 mySQL 表中具有重复项的所有行。我的表格有字段名称日期位置电子邮件，看起来像

+----------+---------------+----------+--------------------+
| M        | 1976-10-03    |        1 | m@gmail            |
| R        | 1982-03-26    |        2 | r@gmail.com        |
| C        | 1987-09-03    |        3 | c@gmail.com        |
| M        | 1976-10-03    |        1 | m@gmail            |
+----------+---------------+----------+--------------------+

我想要得到

+----------+---------------+----------+--------------------+           |
| R        | 1982-03-26    |        2 | r@gmail.com        |
| C        | 1987-09-03    |        3 | c@gmail.com        |
+----------+---------------+----------+--------------------+

我的尝试(来自类似问题的答案)

DELETE FROM ts1 WHERE * IN (SELECT * FROM ts1 GROUP BY * HAVING COUNT(*)>1);

哪里有错误？我知道我使用了太多 * 但我想避免命名所有列，因为它们在我的实际表中太多。请注意，我想检查整行中的重复项。

Answer 1

您不能使用GROUP BY * - 您想使用GROUP BY name:

DELETE FROM ts1 WHERE name IN (SELECT name FROM ts1 GROUP BY name HAVING COUNT(*)>1);

请注意，这将假设用户具有唯一的名称。

所以您实际上可能想检查他们的电子邮件:

DELETE FROM ts1 WHERE email IN (SELECT email FROM ts1 GROUP BY email HAVING COUNT(*)>1);