如何在 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 次檢視

啟動您的 職業生涯

透過完成課程獲得認證

開始