MySQL——删除重复数据

前言

数据导入的时候,导入了重复的数据

内容

结果

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