如何在Oracle中的表中查找和删除重复项？

问题陈述：
您想在Oracle中的表中查找和删除重复项。

解决方案：我们可以使用Oracle的内部ROWID值来唯一标识表中的行。实现此目的的示例语法如下所示。

delete from table
where rowid in
  (... query here ...)

为了演示用法，我们将从创建示例数据开始。

示例

-- table with tennis player rankings
DROP TABLE atp_stats;

CREATE TABLE atp_stats
( player_rank NUMBER NOT NULL,
  player_name VARCHAR2(100) NOT NULL,
  time_range  TIMESTAMP(6));

-- sample records
INSERT INTO atp_stats VALUES (1,'ROGER FEDERER',CURRENT_TIMESTAMP);
INSERT INTO atp_stats VALUES (2,'RAFAEL NADAL',CURRENT_TIMESTAMP);
INSERT INTO atp_stats VALUES (3,'NOVAK DJOKOVIC',CURRENT_TIMESTAMP);
INSERT INTO atp_stats VALUES (4,'ANDY MURRAY',CURRENT_TIMESTAMP);
INSERT INTO atp_stats VALUES (1,'ROGER FEDERER',CURRENT_TIMESTAMP);
INSERT INTO atp_stats VALUES (2,'RAFAEL NADAL',CURRENT_TIMESTAMP);
INSERT INTO atp_stats VALUES (3,'NOVAK DJOKOVIC',CURRENT_TIMESTAMP);
COMMIT;

查看我们刚刚创建的数据。

示例

SELECT * FROM atp_stats ORDER BY 2;

因此，我们插入了3个要删除的重复项。在继续编写Delete语句之前，让我们了解ROWID的内部查询。

示例

SELECT rowid
   FROM (
SELECT player_rank,
  player_rank,
  rowid ,
  row_number() over (partition BY player_rank, player_name order by player_rank,player_name) AS rnk
FROM atp_stats
)
WHERE rnk > 1;

我故意在此最里面的子查询中添加了player_rank和player_name列，以使逻辑易于理解。理想情况下，可以在没有它们的情况下编写最里面的子查询。如果我们只选择最外层的列来执行最深层的查询，就会看到这些结果。

SQL返回表中所有行的rowid。然后，ROW_NUMBER()函数可以处理由PARTITION BY指令驱动的ID和player_name集。这意味着对于每个唯一的player_rank和player_name，ROW_NUMBER将开始对我们别名为rnk的行进行计数。当观察到新的player_rank和player_name组合时，RNK计数器将重置为1。

现在，我们可以应用DELETE运算符来删除重复的值，如下所示。

SQL：删除重复项

示例

DELETE
  FROM atp_stats
 WHERE rowid IN (
                   SELECT rowid
                     FROM(
                          SELECT player_rank,  player_name,
                            rowid ,
                            row_number() over (partition BY player_rank, player_name order by player_rank,player_name) AS rnk
                          FROM atp_stats
                         )
                     WHERE rnk > 1
                );

输出结果

3 rows deleted.