Oracle PL/SQL-如何删除SQL表中的多个重复记录? [英] Oracle PL/SQL - How to delete multiple duplicate records in the SQL table?

问题描述

我的表名TABLE1包含重复的记录，如下所示:

I have table name TABLE1 that contains duplicate records as shown below:

ID  TYPE  AMOUNT NUMBER      DATE
--- ----  ------ ------ ---------
1    AAA   10.00 AAA123 22-JUN-11 
2    AAA    2.00 AAA123 22-JUN-11 
3    AAA   10.00 AAA123 22-JUN-11 
4    AAA    2.00 AAA123 22-JUN-11 
5    AAA   10.00 AAA123 22-JUN-11 
6    AAA    2.00 AAA123 22-JUN-11 
7    AAA   10.00 AAA123 22-JUN-11 
8    AAA    2.00 AAA123 22-JUN-11 
...  ...     ...    ...       ...
100  AAA   10.00 AAA123 22-JUN-11
101  AAA    2.00 AAA123 22-JUN-11

在这种情况下，我想删除所有重复的组合行，除了两个使用SQL或(通过PL/SQL，其中AMOUNT(分别为10.00和2.00)).此外，包含不同数量的重复记录可能会超过两个，如下所示:

In this case, I would like to remove ALL duplicate combination rows, except two using SQL or/via PL/SQL where AMOUNT (10.00 and 2.00). In addition, the duplicate recordes containing different amounts could be more than two such as shown below:

ID  TYPE  AMOUNT NUMBER      DATE
--- ----  ------ ------ ---------
1    AAA   10.00 AAA123 22-JUN-11 
2    AAA    2.00 AAA123 22-JUN-11 
3    AAA   15.00 AAA123 22-JUN-11 
4    AAA   25.50 AAA123 22-JUN-11 
5    AAA   10.00 AAA123 22-JUN-11 
6    AAA    2.00 AAA123 22-JUN-11 
7    AAA   15.00 AAA123 22-JUN-11 
8    AAA   25.50 AAA123 22-JUN-11 
...

在上面的示例中，我只需要删除8条记录中的4条，其中AMOUNT应该保留4条记录(10.00、2.00、15.00和25.50).换句话说，我在一个表中有多组重复项(2条记录一个，4条记录另一个，等等)-多行，其中存在多个.

In the above example, I need to delete only 4 out of 8 records, where AMOUNT should remain 4 records (10.00, 2.00, 15.00 and 25.50). In other words, I have multiple groups of duplicates in one table (2 records for one, 4 for another, etc) - multiple rows where more than one exists.

推荐答案

尝试一下:

DELETE 
    FROM  TABLE1
    WHERE ROWID IN 
    (
        SELECT ROW_ID_VAL
          FROM 
            (
                SELECT a.*, 
                       RANK() OVER(PARTITION BY AMOUNT ORDER BY ID DESC) RN, ROWID row_id_val
                  FROM TABLE1 a
            )
            WHERE rn <> 1
    )

这篇关于Oracle PL/SQL-如何删除SQL表中的多个重复记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！