删除除最近一条记录以外的所有记录？

Question

删除除最近一条记录以外的所有记录？

24

我有两个数据库表，它们之间是一对多的关系。数据如下：

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语句以过滤出正确的记录？

- sim

1

这些学生是否有必要同时拥有多个申请？如果不需要，你可以只建立一对一的关系。 - NullUserException

1

@mu 由于标记为 [plsql]，我认为这是Oracle。 - NullUserException

顺便说一下，我不能更改数据库模式或更改表之间的关系等。 - sim

你的表格长什么样子（例如：DESC student; DESC application）？ - NullUserException

你的选择语句似乎有误，你没有将两个表连接起来。 - user330315

3个回答

8

您可以使用 row_number()（或 rank() 或 dense_rank()，甚至只是 rownum 伪列）对记录应用排序，并使用该排序确定要丢弃哪些记录。在此情况下，按 applicationdatetime desc 排序，为每个学生最近日期的申请分配排名1：

select studentid, applicationid from (
    select studentid, applicationid,
        row_number() over (partition by studentid
            order by applicationdatetime desc) as rn
    from application
)
where rn = 1;

 STUDENTID APPLICATIONID
---------- -------------
         1         20002
         2         20005

您可以删除任何排名高于1的内容，这将保留您关心的记录：

delete from application
where (studentid, applicationid) in (
    select studentid, applicationid from (
        select studentid, applicationid,
            row_number() over (partition by studentid
                order by applicationdatetime desc) as rn
        from application
    )
    where rn > 1
);

3 rows deleted.

- Alex Poole

1

首先，你可以这样做

DELETE FROM [student]
           or [application]
WHERE (studentid, applicationid) NOT IN (SELECT StudentID
                                               ,MAX(ApplicationID)
                                         FROM student
                                             ,application
group by StudentID);

但是还有另一种解决方案，您可以创建备份表，在删除表中的所有记录并插入所需数据（最大值选择在您的表中）之后。

- Tvitmsvleli

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user330315 · Accepted Answer

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

考虑到评论区中的长时间讨论，请注意以下内容：

上述语句将会在任何正确实现了语句级读取一致性的数据库上运行，无论在执行语句时表中是否有任何更改。

已经证实该语句在以下数据库中即使在对表进行并发修改的情况下也能正常运行：Oracle（此问题所涉及的数据库）、Postgres、SAP HANA、Firebird（以及可能使用InnoDB的MySQL）。因为它们都保证从语句启动时的时间点上提供一致的数据视图，将<>更改为<对它们不会有任何影响（包括此问题所涉及的Oracle）

对于上述提到的数据库，该语句不会受到隔离级别的限制，因为幻读或不可重复读只能在多条语句之间发生，而不能在单个语句内发生。

对于不正确实现MVCC、依靠锁定管理并发写入访问（从而阻止并发写入访问）的数据库，如果表同时被更新，则这可能会导致错误结果。对于这些数据库，可能需要使用<的解决方法。