转： https://www.jb51.net/article/116677.htm
前言
本文主要给大家介绍了关于MySQL中查询、删除重复记录的方法，分享出来供大家参考学习，下面来看看详细的介绍：
查找所有重复标题的记录：
| 1 | select title,count``(*)<span> </span>as count from user_table<span> </span>group by title<span> </span>having count>1; |
|---|
| 1 | SELECT *<span> </span>FROM t_info a<span> </span>WHERE ((SELECT COUNT(*)<span> </span>``FROM t_info<span> </span>WHERE Title = a.Title) > 1)<span> </span>ORDER BY Title<span> </span>DESC |
|---|
一、查找重复记录
1、查找全部重复记录
| 1 | SELECT *<span> </span>FROM t_info a<span> </span>WHERE ((SELECT COUNT(*)<span> </span>``FROM t_info<span> </span>WHERE Title = a.Title) > 1)<span> </span>ORDER BY Title<span> </span>DESC |
|---|
2、过滤重复记录(只显示一条)
| 1 | Select *<span> </span>From HZT<span> </span>Where ID<span> </span>In (Select Max(ID)<span> </span>``From HZT<span> </span>Group By Title) |
|---|
注：此处显示ID最大一条记录
二、删除重复记录
1、删除全部重复记录（慎用）
| 1 | Delete 表<span> </span>Where 重复字段<span> </span>In (Select 重复字段<span> </span>From 表<span> </span>Group By 重复字段<span> </span>Having Count(*)>1) |
|---|
2、保留一条（这个应该是大多数人所需要的 ^_^）
| 1 | Delete HZT<span> </span>Where ID<span> </span>Not In (Select Max(ID)<span> </span>``From HZT<span> </span>Group By Title) |
|---|
注：此处保留ID最大一条记录
三、举例
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
| 1 | select *<span> </span>from people<span> </span>where peopleId<span> </span>in (select peopleId<span> </span>from people<span> </span>group by peopleId<span> </span>having count(peopleId) > 1) |
|---|
2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
| 1 | delete from people<span> </span>where peopleId<span> </span>in (select peopleId<span> </span>from people<span> </span>group by peopleId<span> </span>having count(peopleId) > 1)<span> </span>``and rowid<span> </span>not in (select``min(rowid)<span> </span>``from people<span> </span>group by peopleId<span> </span>having count(peopleId )>1) |
|---|
3、查找表中多余的重复记录（多个字段）
| 1 | select *<span> </span>from vitae a<span> </span>where (a.peopleId,a.seq)<span> </span>in (select peopleId,seq<span> </span>from vitae<span> </span>group by peopleId,seq<span> </span>having count(*) > 1) |
|---|
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
| 1 | delete from vitae a<span> </span>where (a.peopleId,a.seq)<span> </span>in (select peopleId,seq<span> </span>from vitae<span> </span>group by peopleId,seq<span> </span>having count(*) > 1)<span> </span>``and rowid<span> </span>not``in (select min(rowid)<span> </span>``from vitae<span> </span>group by peopleId,seq<span> </span>having count(*)>1) |
|---|
5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
| 1 | select *<span> </span>from vitae a<span> </span>where (a.peopleId,a.seq)<span> </span>in (select peopleId,seq<span> </span>from vitae<span> </span>group by peopleId,seq<span> </span>having count(*) > 1)<span> </span>``and rowid<span> </span>not``in (select min(rowid)<span> </span>``from vitae<span> </span>group by peopleId,seq<span> </span>having count(*)>1) |
|---|
四、补充
有两个以上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。
1、对于第一种重复，比较容易解决，使用
| 1 | select distinct *<span> </span>from tableName |
|---|
就可以得到无重复记录的结果集。
如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除
| 1234 | select distinct *<span> </span>into #Tmp<span> </span>from tableNamedrop table tableNameselect *<span> </span>into tableName<span> </span>from #Tmpdrop table #Tmp |
|---|
发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。
2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下
假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集
| 123 | select identity(int``,1,1)<span> </span>as autoID, *<span> </span>into #Tmp<span> </span>from tableNameselect min(autoID)<span> </span>``as autoID<span> </span>into #Tmp2<span> </span>from #Tmp<span> </span>group by Name,autoID``select *<span> </span>from #Tmp<span> </span>where autoID<span> </span>in``(select autoID<span> </span>from #tmp2) |
|---|
总结