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
insert into tb1 select 1 union select 2 union select 3insert 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 , 1select 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
aaa
-----------
1
2
3
4
5其中4、5在表b中未曾出现,如果按照上述的做法,就会出现 4 0 0 0
5 0 0 0请问如何能过滤掉这种情况呢?
insert into tb1 select 1 union select 2 union select 3 union select 4 union select 5insert 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 , 1Select * 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