SQL查重以及删除

查询重复记录

表名: table_name

查询列: column_name

单个条件

select * from table_name 
 where column_name in(select column_name from table_name
 group by column_name having count(column_name)>1)
 order by column_name;

多个条件

select * from table_name as t1 
where 
( 
select count(*) from table_name t2 
where 
t2.column_name_1=t1.column_name_1 and 
t2.column_name_2=t1.column_name_2 and 
t2.column_name_3=t1.column_name_3 and 
t2.column_name_4=t1.column_name_4 
)>1;

删除重复只保留一个

表名: table_name

查询列: column_name

单个条件

delete from table_name 
 where column_name in (select column_name from table_name group by column_name having count(column_name) > 1) 
 and ctid not in (select min(ctid) from table_name group by column_name having count(column_name)>1); 

多个条件

delete from table_name 
 where (column_name1,column_name2,column_name3) in 
 (select column_name1,column_name2,column_name3 from table_name 
 group by column_name1,column_name2,column_name3 
 having count(*) > 1) 
 and ctid not in 
 (select min(ctid) from table_name 
 group by column_name1,column_name2,column_name3 
 having count(*)>1);

https://blog.csdn.net/fm0517/article/details/61202099