欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

sql查出一张表中重复的所有记录数据的三种方法

程序员文章站 2022-06-23 14:24:31
查询重复的数据 1.查询出所有数据进行分组之后,和重复数据的重复次数的查询数据,先列下: select count(username) as '重复次数',username f...

查询重复的数据

1.查询出所有数据进行分组之后,和重复数据的重复次数的查询数据,先列下:

select  count(username) as '重复次数',username from xi group by username  having count(*)>1 order by username desc

2.查找表中多余的重复记录,重复记录是根据单个字段(peopleid)来判断

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

3.查找表中多余的重复记录(多个字段)

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