sql - 删除除最新记录之外的所有记录？

Question

我有两个一对多关系的数据库表。数据如下所示:

select * from student, application

结果集:

+-----------+---------------+---------------------+
| StudentID | ApplicationID | ApplicationDateTime |
+-----------+---------------+---------------------+
| 1         | 20001         | 12 April 2011       |
| 1         | 20002         | 15 May 2011         |
| 2         | 20003         | 02 Feb 2011         |
| 2         | 20004         | 13 March 2011       |
| 2         | 20005         | 05 June 2011        |
+-----------+---------------+---------------------+

我想删除除最近的应用程序之外的所有应用程序。换句话说，每个学生只能有一个链接到它的应用程序。使用上面的示例，数据应如下所示:

+-----------+---------------+---------------------+
| StudentID | ApplicationID | ApplicationDateTime |
+-----------+---------------+---------------------+
| 1         | 20002         | 15 May 2011         |
| 2         | 20005         | 05 June 2011        |
+-----------+---------------+---------------------+

我将如何构建我的 DELETE 语句以过滤掉正确的记录？

Answer 1

DELETE FROM student
WHERE ApplicationDateTime <> (SELECT max(ApplicationDateTime) 
                              FROM student s2
                              WHERE s2.StudentID  = student.StudentID)

鉴于评论中的长时间讨论，请注意以下几点:

以上声明 将无论语句运行时表的任何更改如何，都可以在正确实现语句级别读取一致性的任何数据库上工作。

我绝对知道即使对表进行并发修改也能正常工作的数据库:Oracle(这个问题涉及的那个)、Postgres、SAP HANA、Firebird(很可能是使用 InnoDB 的 MySQL)。因为它们都保证了语句开始时数据的一致 View 。更改 <>至 <不会为他们改变任何东西(包括这个问题所涉及的 Oracle)

对于上述数据库，语句为 不是 受限于隔离级别，因为幻读或不可重复读只能在 之间发生多个 语句 - 不在单个语句中。

对于没有正确实现 MVCC 并依赖锁定来管理并发性(从而阻塞并发写访问)的数据库，如果表同时更新，这实际上可能会产生错误的结果。对于那些使用 < 的解决方法可能需要。