mysql 去重留一

首先先分析一下 我們現在的目的 是 查詢到這倆張表的所有數據 然后進行刪除重復記錄 每條數據只保留一條

第一步：
查詢以下倆張表的重復記錄 (關鍵字段重復>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