前言
数据导入的时候,导入了重复的数据
内容
结果
delete from <table.name> where id in (select id from (select * from <table.name> where wxid in(select wxid from <table.name> group by wxid having count(wxid) >1) and id not in (select min(id) from <table.name> group by wxid having count(wxid)>1)) a);
步骤
查询重复数据的字段
我这里是wxid
select wxid from <table.name> group by wxid having count(wxid) >1;
查询出重复数据字段中最小的自增ID
select min(id) from <table.name> group by wxid having count(wxid)>1;
筛选出将被删除的重复数据
select * from <table.name> where wxid in(select wxid from <table.name> group by wxid having count(wxid) >1) and id not in (select min(id) from <table.name> group by wxid having count(wxid)>1);
将需要被删除的自增ID筛选出来
select id from (select * from <table.name> where wxid in(select wxid from <table.name> group by wxid having count(wxid) >1) and id not in (select min(id) from <table.name> group by wxid having count(wxid)>1)) a;
根据ID删除重复数据
## 先通过select确认没有问题后再使用delete select * from <table.name> where id in (select id from (select * from <table.name> where wxid in(select wxid from <table.name> group by wxid having count(wxid) >1) and id not in (select min(id) from <table.name> group by wxid having count(wxid)>1)) a); ## 真正删除 delete from <table.name> where id in (select id from (select * from <table.name> where wxid in(select wxid from <table.name> group by wxid having count(wxid) >1) and id not in (select min(id) from <table.name> group by wxid having count(wxid)>1)) a);
学无止境,谦卑而行.
