删除除最近的记录以外的所有记录? [英] Delete all records except the most recent one?

问题描述

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

I have two DB tables in a one-to-many relationship. The data looks like this:

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        |
+-----------+---------------+---------------------+

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

I want to delete all applications except for the most recent one. In other words, each student must only have one application linked to it. Using the above example, the data should look like this:

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

我该如何构造我的DELETE语句以过滤出正确的记录?

How would I go about constructing my DELETE statement to filter out the correct records?

推荐答案

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

---

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

---

Given the long discussion in the comments, please note the following:

以上语句将适用于正确实现语句级读取一致性的任何数据库，而不管该语句在运行时对表的任何更改.

The above statement will work on any database that properly implements statement level read consistency regardless of any changes to the table while the statement is running.

我肯定知道即使对表进行并发修改的数据库也可以正常工作:Oracle(此问题所涉及的数据库)，Postgres，SAP HANA，Firebird(最有可能的MySQL使用InnoDB).因为它们都保证了在语句启动时的时间点上的数据具有一致的视图.将<>更改为<不会对其进行任何更改(包括与该问题有关的Oracle)

Databases where I definitely know that this works correctly even with concurrent modifications to the table: Oracle (the one which this question is about), Postgres, SAP HANA, Firebird (and most probably MySQL using InnoDB). Because they all guarantee a consistent view of the data at the point in time when the statement started. Changing the <> to < will not change anything for them (including Oracle which this question is about)

对于上述数据库，该语句不受隔离级别的约束，因为幻像读取或不可重复读取只能在多个语句之间发生-不能在内部单语句.

For the above mentioned databases, the statement is not subject to the isolation level because phantom reads or non-repeatable reads can only happen between multiple statements - not within a single statement.

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

For database that do not implement MVCC properly and rely on locking to manage concurrency (thus blocking concurrent write access) this might actually yield wrong results if the table is updated concurrently. For those the workaround using < is probably needed.

这篇关于删除除最近的记录以外的所有记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！