Oracle如何刪除表中重複記錄

1 引言

在對資料庫進行操作過程中我們可能會遇到這種情況，表中的資料可能重複出現，使我們對資料庫的操作過程中帶來讀諸多不便，那麼怎麼刪除這些重複沒有用的資料呢?

平時工作中可能會遇到當試圖對庫表中的某一列或幾列建立唯一索引時，系統提示 ORA-01452 ：不能建立唯一索引，發現重複記錄。

2 處理過程

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

2.1 刪除重複記錄的方法原理

(1)在Oracle中，每一條記錄都有一個rowid，rowid在整個資料庫中是唯一的，rowid確定了每條記錄是在Oracle中的哪一個資料檔案、塊、行上。

(2)在重複的記錄中，可能所有列的內容都相同，但rowid不會相同，所以只要確定出重複記錄中那些具有最大rowid的就可以了，其餘全部刪除。

2.2 刪除部分欄位重複資料

2.2.1 重複記錄全部刪除

想要刪除部分欄位重複的資料，可以使用下面語句進行刪除，下面的語句是刪除表中欄位1和欄位2重複的資料：

DELETE FROM 表名 a

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 臨時表);

這種先建臨時表再進行刪除的操作要比直接用一條語句進行刪除要高效得多。

例子：

DELETE FROM tmp_lhr t

WHERE (t.accesscode, t.lastserviceordercode, t.serviceinstancecode) IN

(SELECT a.accesscode, a.lastserviceordercode, a.serviceinstancecode

FROM tmp_lhr a

GROUP BY a.accesscode,

a.lastserviceordercode,

a.serviceinstancecode

HAVING COUNT(1) > 1);

2.2.2 保留最新的一條記錄

假如想保留重複資料中最新的一條記錄啊!那怎麼辦呢？在oracle中，有個隱藏了自動rowid，裡面給每條記錄一個唯一的rowid，我們如果想保留最新的一條記錄，我們就可以利用這個欄位，保留重複資料中rowid最大的一條記錄就可以了。

一、如何查詢重複記錄？

SELECT *

FROM TABLE_NAME A

WHERE ROWID NOT IN (SELECT MAX(ROWID)

FROM TABLE_NAME D

WHERE A.COL1 = D.COL1

AND A.COL2 = D.COL2);

二、如何刪除重複記錄？ 1、方法1

DELETE FROM TABLE_NAME

WHERE ROWID NOT IN (SELECT MAX(ROWID)

FROM TABLE_NAME D

group by d.col1,d.col2);

這種方法最簡單！！！

2、方法2

DELETE FROM TABLE_NAME A

WHERE ROWID NOT IN (SELECT MAX(ROWID)

FROM TABLE_NAME D

WHERE A.COL1 = D.COL1

AND A.COL2 = D.COL2);

3、方法3 臨時表

由此，我們要刪除重複資料，只保留最新的一條資料，就可以這樣寫了：

create table 臨時表 as select a.欄位1,a.欄位2,MAX(a.ROWID) dataid from 正式表 a GROUP BY a.欄位1,a.欄位2;

DELETE FROM 正式表 a

where a.rowid NOT IN (SELECT b.dataid

FROM 臨時表 b

WHERE a.欄位1 = b.欄位1

and a.欄位2 = b.欄位2);

commit;

例子：

DELETE FROM tmp_lhr t

WHERE t.rowid not in (SELECT MAX(ROWID)

FROM tmp_lhr a

GROUP BY a.accesscode,

a.lastserviceordercode,

a.serviceinstancecode);

DELETE FROM tmp_lhr t

WHERE t.rowid !=

(SELECT MAX(ROWID)

FROM tmp_lhr a

WHERE a.accesscode = t.accesscode

AND a.lastserviceordercode = t.lastserviceordercode

AND a.serviceinstancecode = t.serviceinstancecode);

2.2.3 刪除以某個欄位為準的記錄

----任意保留一條記錄

DELETE FROM ods_entity_info_full_lhr_01 T

WHERE T.ROWID NOT IN (SELECT MAX(A.ROWID)

FROM ods_entity_info_full_lhr_01 A

GROUP BY entity_code,

entity_type);

---保留 entity_id 最大的一條記錄

DELETE FROM ods_entity_info_full_lhr_01 a

WHERE a.rowid NOT IN

(SELECT t.rowid

FROM ods_entity_info_full_lhr_01 t

WHERE (t.entity_code, t.entity_type, t.entity_id) IN

(SELECT entity_code,

entity_type,

MAX(entity_id)

FROM ods_entity_info_full_lhr_01

GROUP BY entity_code,

entity_type));

2.3 刪除完全重複記錄

對於表中兩行記錄完全一樣的情況，可以用下面三種方式獲取到去掉重複資料後的記錄：

1. select distinct * from 表名;

2. select * from 表名 group by 列名1,列名2,... having count(*)>1

3. select * from 表名 a where rowid<(select max(rowid) from 表名 b where a.列名1=b.列名2 and ...)

2.3.1 方法1

DELETE FROM tmp_lhr t

WHERE t.rowid not in (SELECT MAX(ROWID)

FROM tmp_lhr a

GROUP BY a.accesscode,

a.lastserviceordercode,

a.serviceinstancecode);

2.3.2 方法2

可以將查詢的記錄放到臨時表中，然後再將原來的表記錄刪除，最後將臨時表的資料導回原來的表中。如下：

CREATE TABLE 臨時表 AS (select distinct * from 表名);

truncate table 正式表;

insert into 正式表 (select * from 臨時表);

drop table 臨時表;

2.3.3 方法3

DELETE FROM xr_maintainsite E

WHERE E.ROWID > (SELECT MIN(X.ROWID)

FROM xr_maintainsite X

WHERE X.Maintainid = E.Maintainid

AND x.siteid = e.siteid);--這裡被更新表中所有欄位都需要寫全

2.4 採用row_number分析函式取出重複的記錄然後刪除序號大於1的記錄

給出一個例子：

delete from aa where rowid in(select rid from(select rowid rid,row_number() over (partition by name order by id) as seq from aa) where seq>1);

3 測試案例

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.

4 經驗總結

重複資料刪除技術可以提供更大的備份容量，實現更長時間的資料保留，還能實現備份資料的持續驗證，提高資料恢復服務水平，方便實現資料容災等。Oracle資料庫重複資料刪除技術有如下優勢：更大的備份容量、資料能得到持續驗證、有更高的資料恢復服務水平、方便實現備份資料的容災。

透過摸索，相信你能發現更多更高效刪除Oracle重複資料的方法。

About Me

...............................................................................................................................

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

● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599 微信群：私聊

● 聯絡我請加QQ好友(646634621)，註明新增緣由

● 於 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

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

...............................................................................................................................

拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。