单表 多字段重复 查询

select * from user u
where ( u.username,u.password )
in ( select username,password from user
group by username,password  having count(*) > 1 )

 -----------------------------------------

 

select p1.*  from user p1,user p2
where p1.id<>p2.id and p1.username = p2.username and p1.password =p2.password and p1.sex=p2.sex=true

----------------------------------------

 

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
例二：
select * from testtable
where numeber in (select number from people group by number having count(number) > 1 )
可以查出testtable表中number相同的记录

 

select * from tYHXGX
where SJHM in (select SJHM from tYHXGX group by SJHM having count(SJHM) > 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)

 

delete from tYHXGX1
where SJHM in (select SJHM from tYHXGX1 group by SJHM   having count(SJHM) > 1)
and id not in (select min(id) from tYHXGX1 group by SJHM having count(SJHM )>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)