###查询重复记录
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);