mysql 去重留一

首先先分析一下 我们现在的目的 是 查询到这俩张表的所有数据 然后进行删除重复记录 每条数据只保留一条

第一步：查询以下俩张表的重复记录 (关键字段重复>1)ks_examcity 、 ks_examdistrictselect * from ks_examcity group by examSubjectID,city,province having count(examSubjectID)>1;select * from ks_examdistrict group by examSubjectID,district,city having count(examSubjectID)>1;

第二步：   查询这两张表中 每条记录的第一条记录 (每条记录重复中的第一条 id最小)select min(id)  from ks_examcity group by examSubjectID, city, provincehaving count(examSubjectID)> 1SELECT min(id)  FROM `ks_examdistrict` GROUP BY `examSubjectID`, `district`, `city`HAVING COUNT(`examSubjectID`)> 1

第三步：    联查： 查询所有的重复数据以及重复记录中第一条以外的数据select `examSubjectID`  from ks_examcity group by examSubjectID, city, provincehaving count(examSubjectID)> 1 )   and id not in(select min(id)  from ks_examcity group by examSubjectID, city, provincehaving count(examSubjectID)> 1SELECT `examSubjectID`  from `ks_examdistrict` group by `examSubjectID`, `district`, `city`HAVING COUNT(`examSubjectID`)> 1)   and id not in(SELECT min(id)  FROM `ks_examdistrict` GROUP BY `examSubjectID`, `district`, `city`HAVING COUNT(`examSubjectID`)> 1

第四步：  查询以上查询到的数据的所有id  并以查询到的id作为条件 进行删除delete from `ks_examcity`  where id IN(  select id from (select  id  from ks_examcity where `examSubjectID` in(select `examSubjectID`  from ks_examcity group by examSubjectID, city, provincehaving count(examSubjectID)> 1 )   and id not in(select min(id)  from ks_examcity group by examSubjectID, city, provincehaving count(examSubjectID)> 1)) as  tmpresult)======================================================DELETE  FROM `ks_examdistrict` where id IN(SELECT id  from(select id  from `ks_examdistrict` where `examSubjectID` in(SELECT `examSubjectID`  from `ks_examdistrict` group by `examSubjectID`, `district`, `city`HAVING COUNT(`examSubjectID`)> 1)   and id not in(SELECT min(id)  FROM `ks_examdistrict` GROUP BY `examSubjectID`, `district`, `city`HAVING COUNT(`examSubjectID`)> 1)) as tmpresult)

参考文章 ：https://www.cnblogs.com/jdbeyond/p/8157224.html