SQL查询重复记录

SQL查询重复记录
http://m.cnblogs.com/15628/1534873.html
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)
[图片](二)
[图片]比方说
[图片]在A表中存在一个字段“name”，
[图片]而且不同记录之间的“name”值有可能会相同，
[图片]现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；
[图片]Select Name,Count(*) from A Group By Name Having Count(*) > 1
[图片]如果还查性别也相