如何在 Oracle 中查找并删除表中的重复项?
问题陈述:
您想在 Oracle 中查找并删除表中的重复项。
解决方案:我们可以使用 Oracle 的内部 ROWID 值来唯一标识表中的行。实现此目的的示例语法如下。
delete from table where rowid in (... query here ...)
To demonstrate the usage, we will begin by creating sample data.
示例
-- 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;
| player_rank | player_name |
| 4 | ANDY MURRAY |
| 3 | NOVAK DJOKOVIC |
| 3 | NOVAK DJOKOVIC |
| 2 | RAFAEL NADAL |
| 2 | RAFAEL NADAL |
| 1 | ROGER FEDERER |
| 1 | ROGER FEDERER |
因此，我们插入了 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 列添加到这个最内层子查询中，以使逻辑更容易理解。理想情况下，最内层子查询可以不添加这些列，但效果相同。如果我们只执行这个最内层查询，并选择额外的列以清晰显示，我们就会看到这些结果。
| player_rank | player_name | rowid | rnk |
| 4 | ANDY MURRAY | AAAPHcAAAAAB/4TAAD | 1 |
| 3 | NOVAK DJOKOVIC | AAAPHcAAAAAB/4TAAC | 1 |
| 3 | NOVAK DJOKOVIC | AAAPHcAAAAAB/4TAAG | 2 |
| 2 | RAFAEL NADAL | AAAPHcAAAAAB/4TAAB | 1 |
| 2 | RAFAEL NADAL | AAAPHcAAAAAB/4TAAF | 2 |
| 1 | ROGER FEDERER | AAAPHcAAAAAB/4TAAE | 1 |
| 1 | ROGER FEDERER | AAAPHcAAAAAB/4TAAA | 2 |
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.
| player_rank | player_name |
| 4 | ANDY MURRAY |
| 3 | NOVAK DJOKOVIC |
| 2 | RAFAEL NADAL |
| 1 | ROGER FEDERER |
