请问该怎么写这条sql语句？

[gqzhang]

表1：
aaa
－－－－－－－
1
2
3

表2：
a b c
－－－－－－－－－－－－－－－－
1 2 3
1 2 3
1 2 3
2 3 1
2 3 1
2 3 1

表2中三个字段a、b、c，其值都来源于表1的字段aaa

现想得出：

a中出现的次数 b中出现的次数 c中出现的次数
1 3 0 3
2 3 3 0
3 0 3 3

这条sql语句该怎么写呢？谢！

[xiaomeixiang]

declare @tb1 table(aaa int)
declare @tb2 table(a int,b int ,c int)
insert @tb1 values(1)
insert @tb1 values(2)
insert @tb1 values(3)

insert @tb2 values( 1 , 2 , 3)
insert @tb2 values( 1 , 2 , 3)
insert @tb2 values( 1 , 2 , 3)
insert @tb2 values( 2 , 3 , 1)
insert @tb2 values( 2 , 3 , 1)
insert @tb2 values( 2 , 3 , 1)

select a.aaa,sum(case when a.aaa=b.a then 1 else 0 end) as a中出现的次数,sum(case when a.aaa=b.b then 1 else 0 end) as b中出现的次数,sum(case when a.aaa=b.c then 1 else 0 end) as c中出现的次数 from @tb1 a,@tb2 b group by a.aaa

[remote_peng]

create table tb1 (aaa int)

create table tb2 (a int,b int ,c int)
insert into tb1 select 1 union select 2 union select 3

insert tb2
select 1 , 2 , 3 union all
select 1 , 2 , 3 union all
select 1 , 2 , 3 union all
select 2 , 3 , 1 union all
select 2 , 3 , 1 union all
select 2 , 3 , 1

select a.aaa,
sum(case when a.aaa=b.a then 1 else 0 end) as a中出现的次数,
sum(case when a.aaa=b.b then 1 else 0 end) as b中出现的次数,
sum(case when a.aaa=b.c then 1 else 0 end) as c中出现的次数
from tb1 a,tb2 b group by a.aaa


drop table tb1,tb2

[gqzhang]

谢谢！很直接、详细！

[gqzhang]

请容许我再问：

如果表1当中字段aaa
aaa
-----------
1
2
3
4
5

其中4、5在表b中未曾出现，如果按照上述的做法，就会出现

4 0 0 0
5 0 0 0

请问如何能过滤掉这种情况呢？

[paoluo]

create table tb1 (aaa int)

create table tb2 (a int,b int ,c int)
insert into tb1 select 1 union select 2 union select 3 union select 4 union select 5

insert tb2
select 1 , 2 , 3 union all
select 1 , 2 , 3 union all
select 1 , 2 , 3 union all
select 2 , 3 , 1 union all
select 2 , 3 , 1 union all
select 2 , 3 , 1

Select * from (
select a.aaa,
sum(case when a.aaa=b.a then 1 else 0 end) as a中出现的次数,
sum(case when a.aaa=b.b then 1 else 0 end) as b中出现的次数,
sum(case when a.aaa=b.c then 1 else 0 end) as c中出现的次数
from tb1 a,tb2 b group by a.aaa
) A
Where a中出现的次数<>0 Or b中出现的次数<>0 Or c中出现的次数<>0


drop table tb1,tb2

[gqzhang]

谢了！！