Удалить все записи, кроме самого последнего?

У меня есть две таблицы DB в отношениях "один ко многим". Данные выглядят следующим образом:

select * from student, application

Resultset:

+-----------+---------------+---------------------+
| 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 для фильтрации правильных записей?

Ответ 1

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

Учитывая длительную дискуссию в комментариях, обратите внимание на следующее:

Вышеприведенный оператор будет работать с любой базой данных, которая должным образом реализует согласованность чтения уровня инструкций, независимо от любых изменений в таблице во время выполнения оператора.

Базы данных, где я определенно знаю, что это работает корректно даже с одновременными изменениями в таблице: Oracle (тот, о котором идет речь), Postgres, SAP HANA, Firebird (и, скорее всего, MySQL с использованием InnoDB). Потому что все они гарантируют согласованное представление данных в момент времени, когда выражение началось. Изменение <> на < ничего не изменит для них (включая Oracle, о котором идет речь)

Для вышеупомянутых баз данных утверждение не зависит от уровня изоляции, потому что phantom чтение или чтение без повторения может происходить только между операторами multiple - не в рамках одного заявления.

Для базы данных, которые не реализуют MVCC должным образом и полагаются на блокировку для управления concurrency (таким образом блокируя одновременный доступ к записи), это может привести к неправильным результатам, если таблица обновляется одновременно. Для них, вероятно, необходимо обходное решение с использованием <.

Ответ 2

Вы можете использовать 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.

Ответ 3

Сначала вы можете сделать это

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

но есть другое решение, вы можете создать таблицу резервного копирования после удаления всех записей в таблицах и после вставки ваших данных (что вам нужно) с максимальными значениями, которые вы выбираете в своих таблицах.