select * from TableA where b in (select b from TableA group by b having count(b) > 1)
示例:
查询语句: select * from student where name in (select name from student group by name having count(name ) > 1)
1. sqlserver其他相关的一些查询:
(1)删除表中多余的重复记录,重复记录是根据单个字段(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)
(2)查找表中多余的重复记录(多个字段)
select * from vitae a where (a.peopleId,a.seq) in
(select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
(3)查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a where (a.peopleId,a.seq) in
(select peopleId,seq from vitae group by peopleId,seq havingcount(*) > 1) and
rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)