数据库中重复数据查询和删除

重复数据查询:

select id_number,count(id_number) from human_info group by id_number having count(id_number)>1;

 

删除重复数据:

按rowid,保留rowid最大的数据

delete from human_info where id_number in (select id_number from human_info group by id_number having count(id_number)>1) and rowid not in (select max(rowid) from human_info group by id_number having count(id_number)>1);

 

按入库日期,保留最大日期数据

delete from human_info where id_number in (select id_number from human_info group by id_number having count(id_number)>1) and insert_time not in (select max(insert_time) from human_info group by id_number having count(id_number)>1);

delete from human_info where insert_time not in (select max(insert_time) from human_info group by id_number having count(id_number)>1);(这个貌似也可以).

 

转载于:https://www.cnblogs.com/fxust/p/8384695.html