oracle重複資料刪除資料只保留一條

重複的資料可能有這樣兩種情況，第一種: 表中只有某些欄位一樣，第二種是兩行記錄完全一樣。

一、對於部分欄位重複資料的刪除

1.查詢重複的資料　　

select 欄位1,欄位2, count(*) from 表名 group by 欄位1,欄位2 having count(*) > 1 　　
例：Select owner from dba_tables group by owner having count(*)>1;
Select owner from dba_tables group by owner having count(*)=1; //查詢出沒有重複的資料　
　
2.重複資料刪除的資料

delete from 表名 a where 欄位1,欄位2 in (select 欄位1,欄位2,count(*) from 表名 group by 欄位1,欄位2 having count(*) > 1)
這種刪除執行的效率非常低，對於大資料量來說，可能會將資料庫弔死。
另一種高效率的方法是先將查詢到的重複的資料插入到一個暫存資料表中，然後再進行刪除。

CREATE TABLE 暫存資料表 AS
(
select 欄位1,欄位2, count(*) as row_num
from 表名
group by 欄位1,欄位2
having count(*) > 1
);
　　上面這句話就是建立了暫存資料表，並將查詢到的資料插入其中。
　　下面就可以進行這樣的刪除操作了：
delete from 表名 a
where 欄位1,欄位2 in (select 欄位1，欄位2 from 暫存資料表); 　　
 
3.保留重複資料中最新的一條記錄

在Oracle中，rowid是隱藏欄位，用來唯一標識每條記錄。所以，只要保留重複資料中rowid最大的一條記錄就可以了。

1、尋找表中多餘的重複記錄，重複記錄是根據單個欄位（Id）來判斷

select * from 表 where Id in (select Id from 表 group byId having count(Id) > 1)

 

2、刪除表中多餘的重複記錄，重複記錄是根據單個欄位（Id）來判斷，只留有rowid最小的記錄

DELETE from 表 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) > 1);

 

3、尋找表中多餘的重複記錄（多個欄位）

select * from 表 a where (a.Id,a.seq) in(select Id,seq from 表 group by Id,seq having count(*) > 1)

 

4、刪除表中多餘的重複記錄（多個欄位），只留有rowid最小的記錄

delete from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

 

5、尋找表中多餘的重複記錄（多個欄位），不包含rowid最小的記錄

select * from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

　　
查詢重複資料:

select a.rowid,a.* from 表名 a
where a.rowid != (
select max(b.rowid) from 表名 b
where a.欄位1 = b.欄位1 and a.欄位2 = b.欄位2 ); 　
　
例：selete from dba_tables a

where a.rowid!=(
select max(rowid) from test b
where a.owner=b.owner);

delete from 表名 a
where a.rowid != (
select max(b.rowid) from 表名 b
where a.欄位1 = b.欄位1 and a.欄位2 = b.欄位2 )

使用暫存資料表實現高效查詢

create table 暫存資料表 as
（select a.欄位1, a.欄位2, MAX(a.ROWID) as dataid from 正式表 a
GROUP BY a.欄位1,a.欄位2);
delete from 表名 a
where a.rowid !=
( select b.dataid from 暫存資料表 b
where a.欄位1 = b.欄位1 and
a.欄位2 = b.欄位2 );
commit;