sql查找既有a又有b

[mike089]

id type
1 a
1 b
1 c
1 d
2 a
3 g
4 b

sql查找既有a又有b的id

结果
1

[快溜]

select a.id from
(select id from tb where type='a') a,
(select id from tb where type='b') b
where a.id=b.id

[mike089]

还有更好的方法吗

[javatemptation]

use tempdb;
/*
create table test
(
	id int not null,
	[type] varchar(5) not null
);
insert into test(id,[type])
values
(1,'a'),
(1,'b'),
(1,'c'),
(1,'d'),
(2,'a'),
(3,'g'),
(4,'b');
*/
select t1.id
from test as t1
join test as t2
on t1.id = t2.id
where t1.type = 'a'
and t2.type = 'b';

[fcuandy]

select id from tb where tpye in ('a','b') group by id having count(distinct type)=2

[AcHerat]

select id
from tb t
where [type] = 'a' and exists (select 1 from tb where id = t.id and [type] = 'b')

[gaogaohui8209]

[Quote=引用 5 楼 acherat 的回复:]
SQL code

select id
from tb t
where [type] = 'a' and exists (select 1 from tb where id = t.id and [type] = 'b')
[/Quote]

up

[fcuandy]

如果是又有a,又有b,c,d,e... 要写5个join还是5个exists? hehe

[--小F--]

[Quote=引用 4 楼 fcuandy 的回复:]
select id from tb where tpye in ('a','b') group by id having count(distinct type)=2
[/Quote]
顶

[叶子]

[Quote=引用 4 楼 fcuandy 的回复:]
select id from tb where tpye in ('a','b') group by id having count(distinct type)=2
[/Quote]
+1

[zhaozhen2004]

select id
from test
where type = 'a' or type = 'b'
group by id
having count(id) > 1

[zhaozhen2004]

select id
from test
where type in ('a','b')
group by id
having count(id) > 1

[benluobobo123]

if object_id('A') is not null
drop table A 

create table A(id int, type varchar(10))

insert into A 
select 1 ,'a' union all 
select 1 ,'b' union all 
select 1 ,'c' union all 
select 1 ,'d' union all 
select 2 ,'a' union all 
select 3 ,'g' union all 
select 4 ,'b' 

select id from A where type='a'
intersect
select id from A where type='b'