mysql查询去重第一条,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://blog.51cto.com/13922718/2433185