如何在 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.

Kiran P

更新于: 2020-12-04

831 次查看

启动您的 职业生涯

通过完成课程获得认证

开始