如何用SQL删除重复的行？

I have a table with some rows in. Every row has a date-field. Right now, it may be duplicates of a date. I need to delete all the duplicates and only store the row with the highest id. How is this possible using a SQL query?

我有一个包含一些行的表。每行都有一个日期字段。现在，它可能与日期重复。我需要删除所有重复项，只存储具有最高id的行。如何使用SQL查询？

Now:

现在：

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

What I want:

我想要的是：

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

3 个解决方案

#1

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

#2

I don't have comment rights, so here's my comment as an answer in case anyone comes across the same problem:

我没有评论权，所以这是我的评论作为答案，以防任何人遇到同样的问题：

In SQLite3, there is an implicit numerical primary key called "rowid", so the same query would look like this:

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

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

this will work with any table even if it does not contain a primary key column called "id".

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

#3

For mysql,postgresql,oracle better way is SELF JOIN.

对于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 aggregate (max,group by) functions almost always are very slow.

SQL聚合（max，group by）函数几乎总是很慢。

#1

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

#2