旗下导航:搜·么
当前位置:网站首页 > MySQL教程 > 正文

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

作者:搜教程发布时间:2019-12-01分类:MySQL教程浏览:35评论:0


导读:本文主要给人人引见了关于MySQL中查询、删除反复纪录的要领,分享出来供人人参考进修,下面来看看细致的引见。查找一切反复题目的纪录:selecttitle,c...
本文主要给人人引见了关于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网别的相干文章!

标签:MySQL


欢迎 发表评论: