mysql去重留一

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

新北ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为成都创新互联的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：18980820575（备注：SSL证书合作）期待与您的合作！

第一步：
查询以下俩张表的重复记录 (关键字段重复>1)
ks_examcity 、 ks_examdistrict

select * 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, province
having count(examSubjectID)> 1

SELECT min(id)
  FROM `ks_examdistrict`
 GROUP BY `examSubjectID`, `district`, `city`
HAVING COUNT(`examSubjectID`)> 1

第三步：
    联查： 查询所有的重复数据以及重复记录中第一条以外的数据
select `examSubjectID`
  from ks_examcity
 group by examSubjectID, city, province
having count(examSubjectID)> 1 )
   and id not in(
select min(id)
  from ks_examcity
 group by examSubjectID, city, province
having count(examSubjectID)> 1

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

第四步：  查询以上查询到的数据的所有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, province
having count(examSubjectID)> 1 )
   and id not in(
select min(id)
  from ks_examcity
 group by examSubjectID, city, province
having 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

分享名称：mysql去重留一
转载来源：http://scyanting.com/article/jpoocd.html

mysql去重留一

其他资讯