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

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

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

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

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

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

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

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

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

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

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

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