SQL表内查重和删重

版权声明:本文为博主原创文章,转载请注明【转载自皓月如我的CSDN博客】,并贴出原始链接地址。 https://blog.csdn.net/fm0517/article/details/61202099

查询重复记录

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); 

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);
阅读更多
换一批

没有更多推荐了,返回首页