首页 / MYSQL / mysql 去重留一
mysql 去重留一
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql 去重留一,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2114字,纯文字阅读大概需要4分钟。
内容图文
![mysql 去重留一](/upload/InfoBanner/zyjiaocheng/893/5cd85076e08e4a48ae9483ec674fb2f0.jpg)
首先先分析一下 我们现在的目的 是 查询到这俩张表的所有数据 然后进行删除重复记录 每条数据只保留一条
第一步:
查询以下俩张表的重复记录 (关键字段重复>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 去重留一全部内容,希望文章能够帮你解决mysql 去重留一所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。