查重的sql oracle,SQL表内查重和删重

###查询重复记录

select * from my_tab

where dup_col in(select dup_col from my_tab

group by dup_col having count(dup_col)>1)

order by dup_col;

###查询重复记录(多个条件)

select * from my_tab as t1

where

(

select count(*) from my_tab t2

where

t2.col_1=t1.col_1 and

t2.col_2=t1.col_2 and

t2.col_3=t1.col_3 and

t2.col_4=t1.col_4

)>1;

###删除重复记录(只留一条)

PG：使用ctid

delete from my_tab

where dup_col in (select dup_col from my_tab group by dup_col having count(dup_col) > 1)

and ctid not in (select min(ctid) from my_tab group by dup_col having count(dup_col)>1);

delete from table_1

where (col_1,col_2,col_3) in

(select col_1,col_2,col_3 from table_1

group by col_1,col_2,col_3

having count(*) > 1)

and ctid not in

(select min(ctid) from table_1

group by col_1,col_2,col_3

having count(*)>1);

ORACLE：使用rowid

delete from my_tab

where dup_col in (select dup_col from my_tab group by dup_col having count(dup_col) > 1)

and rowid not in (select min(rowid) from my_tab group by dup_col having count(dup_col)>1);