Oracle PL/SQL - 如何在SQL表中删除多个重复记录？

Question

我有一个名为TABLE1的表，其中包含如下所示的重复记录：

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或/via PL/SQL删除所有重复的组合行，除了AMOUNT（10.00和2.00）之外的两个。此外，包含不同金额的重复记录可能超过两个，如下所示：

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条记录等）-存在多行，其中存在多个相同的记录。

- valmont74

---

那么哪些列会导致重复？只有金额吗？ - Chandu

社区不赞成在答案只使用SQL时标记PLSQL。针对哪个版本的Oracle？ - OMG Ponies

Answer 1

试试这个：

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
    )

- Chandu

---

在这种情况下，“ROW_NUMBER”可能是更好的选择？ - OMG Ponies

@OMG Pointers：我假设ID字段是唯一的，因为我正在使用ID对窗口中的数据进行排序，所以RANK应该还可以。 - Chandu

Answer 2

日期列是唯一需要更多信息的列；否则使用：

DELETE FROM YOUR_TABLE
 WHERE EXISTS (SELECT NULL
                 FROM YOUR_TABLE t
                WHERE t.type = YOUR_TABLE.type
                  AND t.amount = YOUR_TABLE.amount
                  AND t.number = YOUR_TABLE.number
                  AND t.date = YOUR_TABLE.date
             GROUP BY t.type, t.amount, t.number, t.date
               HAVING MIN(t.id) != YOUR_TABLE.id)

YOUR_TABLE.列引用指的是外部的YOUR_TABLE，即要执行删除操作的表。这使它具有相关子查询的效果，但EXISTS的功能不完全是这样。

- OMG Ponies

Answer 3

你可以按照以下步骤进行：

每一行都有一个唯一的 (row id)。当然，你可以识别出重复的行，然后根据 (row id) 删除重复的行。只需输入以下 SELECT 语句，以显示重复的行 id:

SELECT rowid from table_name;

- Mussa

Answer 4

DECLARE

BEGIN

  for rec_ in (
                 SELECT type, amount, number, date , count(1) record_count
                    FROM table 1
                  GROUP BY type, amount, number, date
                 HAVING count(1) > 1) loop

      counter_ := 0;

      for rec2_ in ( select * from table1 where rec_.type = type
                                            and rec_.amount = amount
                                            and rec_.number = number
                                            and rec_.date = date) loop

            counter_ := counter_ + 1;            
            exit when counter_ = rec_.record_count;
            delete from table1 where id = rec2_.id;
       End loop;

  end loop;
END;

- Calipso