create table #t(id int, bm varchar(100))insert into #t(id,bm) values(1,'A') insert into #t(id,bm) values(1,'B') insert into #t(id,bm) values(1,'C') insert into #t(id,bm) values(2,'D') insert into #t(id,bm) values(2,'E') insert into #t(id,bm) values(3,'F')--提取id重复出现的行 select * from #t where id in (select id from #t group by id having count(*)>1)--或 select * from #t where id not in (select id from #t group by id having count(*)=1)drop table #t
select * from #t tb where id not in(select id from #t group by id having count(id)=1)
select a.id,a.name from aa as a ,aa as b where aa.name=bb.name and aa.id <> bb.id 能看懂吧?
select * from #t A where not exists (select 1 from #t where A.id=id group by id having count(id)=1)
insert into #t(id,bm) values(1,'B')
insert into #t(id,bm) values(1,'C')
insert into #t(id,bm) values(2,'D')
insert into #t(id,bm) values(2,'E')
insert into #t(id,bm) values(3,'F')--提取id重复出现的行
select * from #t where id in (select id from #t group by id having count(*)>1)--或
select * from #t where id not in (select id from #t group by id having count(*)=1)drop table #t
where aa.name=bb.name and aa.id <> bb.id 能看懂吧?
from #t A
where not exists
(select 1 from #t where A.id=id group by id having count(id)=1)