在Oracle SQL中删除重复的行，保留最新的条目 [英] Delete duplicate rows in Oracle SQL, leaving the latest entries

问题描述

我正在使用以下SQL来识别表'transaction_list'中的重复项.这完美地工作.现在，我想根据这些条件从该表中删除所有重复项，而只保留最新的条目.这些可以通过"last_update"列来标识.我尝试了不同的DELETE语句，但是没有用.任何建议都将受到高度赞赏.

I'm using the following SQL to identify duplicates in the table 'transaction_list'. This works perfectly. Now I want to delete all duplicates from that table based on these criteria and leave only the latest entries. These can be identified by the column 'last_update'. I tried different DELETE statements but it didn't work. Any suggestions are highly appreciated.

SELECT par_num
,tran_num
,COUNT(*) AS num_duplicates
FROM transaction_list
WHERE last_update >= to_date('01-mar-2020 00:00:00', 'dd-mon-yyyy 
hh24:mi:ss')
GROUP BY par_num
,tran_num
HAVING COUNT(*) > 1
ORDER BY par_num

推荐答案

如果要删除所有 par_num / tran_num 重复项，但按顺序排序的每个集合中的最后一个除外 last_update ，那么应该这样做:

If the idea is to delete all par_num / tran_num duplicates except the last in each set ordered by last_update, then this should do it:

delete transaction_list
where  rowid in
       ( select lag(rowid)
                over (partition by par_num, tran_num order by last_update)
         from   transaction_list );

DBFiddle

说明: lag 返回上一行的值(或另一先前的行-如果需要，您可以指定各种偏移量逻辑，但是在这里我们只需要上一行). over() 子句指定顺序和窗口.在这种情况下，我们要通过 last_update 对每组 par_num / tran_num 组合进行排序，并删除上一行. partition by 部分表示每个 par_num / tran_num 组合的排序重置，因此每个组都有一个最后"行，不会删除.

Explanation: lag returns a value from the previous row (or another earlier row - you can specify all kinds of offset logic if you want, but here we just want the previous row). The over() clause specifies the ordering and windowing. In this case, we want to order each set of par_num / tran_num combinations by last_update and delete the previous row. The partition by section means the ordering resets for each par_num / tran_num combination, so each group has a 'last' row that won't be deleted.

这篇关于在Oracle SQL中删除重复的行，保留最新的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！