【DB筆試面試469】Oracle中如何刪除表中重複的記錄？

• 2019 年 10 月 11 日
• 筆記

題目部分

Oracle中如何刪除表中重複的記錄？

答案部分

平時工作中可能會遇到這種情況，當試圖對表中的某一列或幾列創建唯一索引時，系統提示ORA-01452 ：不能創建唯一索引，發現重複記錄。這個時候只能創建普通索引或者刪除重複記錄後再創建唯一索引。

重複的數據可能有這樣兩種情況：第一種是表中只有某些欄位一樣，第二種是兩行記錄完全一樣。刪除重複記錄後的結果也分為兩種，第一種是重複的記錄全部刪除，第二種是重複的記錄中只保留最新的一條記錄，在一般業務中，第二種的情況較多。

1、刪除重複記錄的方法原理

在Oracle中，每一條記錄都有一個ROWID，ROWID在整個資料庫中是唯一的，ROWID確定了每條記錄是在Oracle中的哪一個數據文件、塊、行上。在重複的記錄中，可能所有列上的內容都相同，但ROWID不會相同，所以，只要確定出重複記錄中那些具有最大ROWID的就可以了，其餘全部刪除。

2、刪除重複記錄的方法

若想要刪除部分欄位重複的數據，則使用下面語句進行刪除，下面的語句是刪除表中欄位1和欄位2重複的數據：

DELETE FROM 表名

WHERE (欄位1, 欄位2) IN (SELECT 欄位1,欄位2 FROM 表名 GROUP BY 欄位1,欄位2 HAVING COUNT(1) > 1);

也可以利用臨時表的方式，先將查詢到的重複的數據插入到一個臨時表中，然後進行刪除，這樣，執行刪除的時候就不用再進行一次查詢了。如下所示：

CREATE TABLE 臨時表 AS (SELECT 欄位1,欄位2,COUNT(*) FROM 表名 GROUP BY 欄位1,欄位2 HAVING COUNT(*) > 1);

上面這句話的功能是建立臨時表，並將查詢到的數據插入其中。有了上面的執行結果，下面就可以進行刪除操作了：

DELETE FROM 表名 A WHERE (欄位1,欄位2) IN (SELECT 欄位1，欄位2 FROM 臨時表);

假如想保留重複數據中最新的一條記錄，應該怎麼做呢？可以利用ROWID，保留重複數據中ROWID最大的一條記錄即可，如下所示：

DELETE FROM TABLE_NAME WHERE ROWID NOT IN (SELECT MAX(ROWID)  FROM TABLE_NAME D GROUP BY D.COL1,D.COL2);

下面給出一個示例：

SYS@raclhr1> CREATE TABLE T_ROWS_LHR_20160809 AS SELECT * FROM SCOTT.EMP;  Table created.  SYS@raclhr1> INSERT INTO T_ROWS_LHR_20160809 SELECT * FROM T_ROWS_LHR_20160809;

14 rows created.

SYS@raclhr1> COMMIT;  Commit complete.  SYS@raclhr1> INSERT INTO T_ROWS_LHR_20160809 SELECT * FROM T_ROWS_LHR_20160809;  28 rows created.  SYS@raclhr1> COMMIT;  Commit complete.  SYS@raclhr1> SELECT COUNT(1) FROM T_ROWS_LHR_20160809;    COUNT(1)  ----------          56  SYS@raclhr1> DELETE FROM T_ROWS_LHR_20160809    2      WHERE ROWID NOT IN  (SELECT MAX(ROWID)    3                        FROM T_ROWS_LHR_20160809 D    4                       group by D.EMPNO,D.ENAME,D.JOB,D.MGR,D.DEPTNO);  42 rows deleted.  SYS@raclhr1> SELECT COUNT(1) FROM T_ROWS_LHR_20160809;    COUNT(1)  ----------          14  SYS@raclhr1> COMMIT;  Commit complete.

重複數據刪除技術可以提供更大的備份容量，實現更長時間的數據保留，還能實現備份數據的持續驗證，提高數據恢復服務水平，方便實現數據容災等。

& 說明：

有關刪除重複數據更多的內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2123234/

本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。

About Me：小麥苗

● 本文作者：小麥苗，只專註於資料庫的技術，更注重技術的運用

● 作者部落格地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列題目來源於作者的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

● 題目解答若有不當之處，還望各位朋友批評指正，共同進步