求 一个SQL语句
已知： B1,B2,B3 字段A ，字段B 是主建
字段A 字段B 字段C
A1 B1 C1
A1 B2 C1
A1 B3 C1
A2 B1 C2
A2 B2 C3
A2 B3 C2
A3 B4 C2
A4 B1 C3
A4 B2 C3
A4 B3 C3
目的：抽出同时包含 B1,B2,B3 的字段A，字段B的数据
结果应该为以下数据
A1 B1 C1
A1 B2 C1
A1 B3 C1
A4 B1 C3

A4 B2 C3
A4 B3 C3
谢谢！



[解决办法]

declare @a table(name1 varchar(20),name2 varchar(20),name3 varchar(20))
insert @a
select 'A1 ', 'B1 ', 'C1 '
union all
select 'A1 ', 'B2 ', 'C1 '
union all
select 'A1 ', 'B3 ', 'C2 '
union all
select 'A2 ', 'B1 ', 'C2 '
union all
select 'A2 ', 'B2 ', 'C3 '
union all
select 'A2 ', 'B3 ', 'C2 '
union all
select 'A2 ', 'B4 ', 'C2 '
union all
select 'A4 ', 'B1 ', 'C3 '
union all
select 'A4 ', 'B2 ', 'C3 '
union all
select 'A4 ', 'B3 ', 'C3 '

select * from @a where name1 in(
select name1 from @a group by name1 having count(1)=(len( 'b1,b2,b3 ')-2)/2)