Oracle如何根据多个数据库列和条件删除重复项(Oracle How delete duplicates based on multiples database columns and conditions)

 我有一个非常庞大的表，其中包含从多个系统收集的数据。 我现在必须根据条件和多列删除重复记录。 
 
 这是一个例子： 
 
 +---------------+-------------+------+-----+--------
| System ID     | Debt Num    | Exp Dt | Account NO |
+---------------+-------------+------+-----+---------
| pay           | 2222        | 0114   |   111      |
| pay           | 2222        | 0214   |   111      |
| Online        | 2222        | 0214   |   111      |
| Online        | 3333        | 0115   |   222      |
| Online        | 3333        | 0116   |   222      |
| ERP           | 2222        | 0214   |   111      | 
| ERP           | 4444        | 0114   |   333      | 
+---------------+-------------+------+-----+--------
 
 从以上数据中删除满足以下条件的重复项。 
 
 
 
 通过debit num，exp dt，account-no删除重复的行组，并使用max（exp dt）保留一条记录。 
 
 
 要保留的记录基于System ID的优先级。 1）支付2）在线和3）ERP。 在上面的帐户111中，我们有来自所有三个系统的记录，并且借记卡的max（exp dt）是所有三个系统中的0214。 只保留Pay with exp dt = 0214的记录，并删除其余部分。 
 
 
 如上面的帐户222示例，我们没有来自pay的记录，因此优先考虑系统Online和ERP，其中应保留max（exp dt）。 
 
 
 我尝试过在线发现的多个查询，例如group by，row_number over，但所有只满足一个条件。 
 
 感谢您的帮助，提出您的想法和建议。 
 
 EDIT ：戈登的查询工作正常，符合我的要求，但是当我在包含540K行的分段上运行相同时，它出错了ORA-00600内部错误。 

I have a very huge table with data collected from multiple systems. I now have to delete duplicate records based on conditions and multiple columns.
 
Here is an example:
 
 +---------------+-------------+------+-----+--------
| System ID     | Debt Num    | Exp Dt | Account NO |
+---------------+-------------+------+-----+---------
| pay           | 2222        | 0114   |   111      |
| pay           | 2222        | 0214   |   111      |
| Online        | 2222        | 0214   |   111      |
| Online        | 3333        | 0115   |   222      |
| Online        | 3333        | 0116   |   222      |
| ERP           | 2222        | 0214   |   111      | 
| ERP           | 4444        | 0114   |   333      | 
+---------------+-------------+------+-----+--------
 
From the above data , delete the duplicates with the following conditions satisfied.
 
 
 
Delete the duplicate rows group by debit num , exp dt , account-no and keep one record with max(exp dt).
 
 
Records to be retained is based of priority to System ID. 1) pay 2) Online and 3) ERP. In the above for the account 111 we have records from all three systems and max(exp dt) of the debit card is 0214 of all three system. Only the record from Pay with exp dt=0214 should be retained and rest should be deleted. 
 
 
As above example for account 222 , we dont have records from pay and hence give priority to system Online and the ERP out of which the max(exp dt) should be retained.
 
 
I have tried multiple queries found online , like group by, row_number over but all fulfill just one condition.
 
Appreciate your help to contribute your thoughts and suggestions. 
 
EDIT: Gordon's query works fine and fits my requirements, however when I ran the same on staging which contains 540K rows, it error out ORA-00600 Internal error.

原文：https://stackoverflow.com/questions/21354812