如何使用SQL删除重复的行？

Question

我有一个包含多行数据的表格，每一行都有一个日期字段。目前，可能存在日期重复的情况。我需要删除所有重复的行，只保留具有最高id值的行。如何使用SQL查询实现此目的？

date      id
'07/07'   1
'07/07'   2
'07/07'   3
'07/05'   4
'07/05'   5

我想要的是：

date      id
'07/07'   3
'07/05'   5

- MrM

---

从您发送的数据中，您最终得到了两行而不是三行！07/05被重复了。 - notnoop

Answer 1

DELETE FROM table WHERE id NOT IN
    (SELECT MAX(id) FROM table GROUP BY date);

- Georg Schölly

---

哇，我绕了一个大圈子，不是吗？这肯定是做这件事的最佳方式。 - Eric

我认为你的方法有点太复杂了...但说实话，我想先使用3个查询来完成它，而不仅仅是一个。 - Georg Schölly

这个查询也对于此答案有帮助：SELECT date, COUNT(date) AS NumOccurrences FROM table GROUP BY date HAVING ( COUNT(date) > 1 )（说明：此查询可用于统计数据表中重复日期的次数，并且仅返回出现超过一次的日期及其出现次数。） - djangofan

@djangofan：差不多了，你只需要选择id而不是COUNT(date)。 - Georg Schölly

然而，由于MySQL在子查询方面的愚蠢限制，这种方法不起作用。 - user330315

根据您的数据，这个查询可能比iddqd建议的查询时间要长得多。 - daSong

Answer 2

由于我没有评论权限，所以在这里用答案的形式发表我的评论，以防其他人遇到同样的问题：

在SQLite3中，有一个名为“rowid”的隐式数字主键，因此相同的查询将如下所示：

DELETE FROM table WHERE rowid NOT IN
(SELECT MAX(rowid) FROM table GROUP BY date);

这将适用于任何表，即使它不包含名为"id"的主键列。

- DaGaMs

Answer 3

对于mysql、postgresql、oracle等数据库，更好的方法是使用自连接（SELF JOIN）。

Postgresql:
DELETE FROM table t1 USING table t2 WHERE t1.date=t2.date AND t1.id<t2.id;

MySQL        
DELETE FROM table
USING table, table as vtable
WHERE (table.id < vtable.id)
AND (table.date=vtable.date)

SQL聚合函数（max，group by）几乎总是非常缓慢的。

- baklarz2048