MySQL中查询、删除重复记录的要领大全【MySQL教程】,MySQL

本文主要给人人引见了关于MySQL中查询、删除反复纪录的要领，分享出来供人人参考进修，下面来看看细致的引见。

查找一切反复题目的纪录：

select title,count(*) as count from user_table group by title having count>1;

SELECT * FROM t_info a WHERE ((SELECT COUNT(*) FROM t_info WHERE Title = a.Title) > 1) ORDER BY Title DESC

一、查找反复纪录

1、查找悉数反复纪录

SELECT * FROM t_info a WHERE ((SELECT COUNT(*) FROM t_info WHERE Title = a.Title) > 1) ORDER BY Title DESC

2、过滤反复纪录(只显现一条)

Select * From HZT Where ID In (Select Max(ID) From HZT Group By Title)

注：此处显现ID最大一条纪录

二、删除反复纪录

1、删除悉数反复纪录（慎用）

Delete 表 Where 反复字段 In (Select 反复字段 From 表 Group By 反复字段 Having Count(*)>1)

2、保存一条（这个应该是大多数人所须要的 ^_^）

Delete HZT Where ID Not In (Select Max(ID) From HZT Group By Title)

注：此处保存ID最大一条纪录

三、举例

1、查找表中过剩的反复纪录，反复纪录是依据单个字段（peopleId）来推断

select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、删除表中过剩的反复纪录，反复纪录是依据单个字段（peopleId）来推断，只留有rowid最小的纪录

delete from people where 
peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 
and rowid not in (select min(rowid) from people group by 
peopleId having count(peopleId )>1)

3、查找表中过剩的反复纪录（多个字段）

select * from vitae a where (a.peopleId,a.seq) in 
(select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、删除表中过剩的反复纪录（多个字段），只留有rowid最小的纪录

delete from vitae a where (a.peopleId,a.seq) in 
(select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中过剩的反复纪录（多个字段），不包括rowid最小的纪录

select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae 
group by peopleId,seq having count(*) > 1) 
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

四、补充

有两个以上的反复纪录，一是完整反复的纪录，也即一切字段均反复的纪录，二是部份症结字段反复的纪录，比方Name字段反复，而其他字段不肯定反复或都反复能够疏忽。

1、关于第一种反复，比较轻易处理，运用

select distinct * from tableName

就能够获得无反复纪录的效果集。

假如该表须要删除反复的纪录（反复纪录保存1条），能够按以下要领删除

select distinct * into #Tmp from tableName 
drop table tableName 
select * into tableName from #Tmp 
drop table #Tmp

发作这类反复的原因是表设想不周发生的，增添唯一索引列即可处理。

2、这类反复题目一般请求保存反复纪录中的第一条纪录，操作要领以下

假设有反复的字段为Name,Address，请求获得这两个字段唯一的效果集

select identity(int,1,1) as autoID, * into #Tmp from tableName 
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID 
select * from #Tmp where autoID in(select autoID from #tmp2)

总结

以上就是这篇文章的悉数内容了，愿望本文的内容对人人的进修或许事情能带来肯定的协助

引荐进修：MySQL教程

以上就是MySQL中查询、删除反复纪录的要领大全的细致内容，更多请关注ki4网别的相干文章！