select t0.name,
sum(decode(t1.flag,'Y',1,0)) table1Y,
sum(decode(t1.flag,'N',1,0)) table1N,
sum(decode(t2.flag,'Y',1,0)) table2Y,
sum(decode(t2.flag,'N',1,0)) table2N,
sum(decode(t3.flag,'Y',1,0)) table3Y,
sum(decode(t3.flag,'N',1,0)) table3N,
sum(decode(t4.flag,'Y',1,0)) table4Y,
sum(decode(t4.flag,'N',1,0)) table4N
from ( select name from table1
union
select name from table2
union
select name from table3
union
select name from table4) t0,
table1 t1,
table2 t2,
table3 t3,
table4 t4
where t0.name=t1.name (+)
and t0.name=t2.name (+)
and t0.name=t3.name (+)
and t0.name=t4.name (+)
group by t0.name
sum(decode(t1.flag,'Y',1,0)) table1Y,
sum(decode(t1.flag,'N',1,0)) table1N,
sum(decode(t2.flag,'Y',1,0)) table2Y,
sum(decode(t2.flag,'N',1,0)) table2N,
sum(decode(t3.flag,'Y',1,0)) table3Y,
sum(decode(t3.flag,'N',1,0)) table3N,
sum(decode(t4.flag,'Y',1,0)) table4Y,
sum(decode(t4.flag,'N',1,0)) table4N
from ( select name from table1
union
select name from table2
union
select name from table3
union
select name from table4) t0,
table1 t1,
table2 t2,
table3 t3,
table4 t4
where t0.name=t1.name (+)
and t0.name=t2.name (+)
and t0.name=t3.name (+)
and t0.name=t4.name (+)
group by t0.name
sum(decode(TF,1,flagN,0))as table1N ,
sum(decode(TF,2,flagY,0))as table2Y ,
sum(decode(TF,2,flagN,0))as table2Y ,
sum(decode(TF,3,flagY,0))as table3Y ,
sum(decode(TF,3,flagN,0))as table3Y ,
sum(decode(TF,4,flagY,0))as table4Y ,
sum(decode(TF,4,flagN,0))as table4Y
from
( select 1 TF ,name , sum(decode(flag,Y,1,0)) flagY , sum(decode(flag,N,1,0)) flagN
from table1 group by name
union
select 2 TF ,name , sum(decode(flag,Y,1,0)) flagY , sum(decode(flag,N,1,0)) flagN
from table2 group by name
union
select 3 TF ,name , sum(decode(flag,Y,1,0)) flagY , sum(decode(flag,N,1,0)) flagN
from table3 group by name
union
select 4 TF ,name , sum(decode(flag,Y,1,0)) flagY , sum(decode(flag,N,1,0)) flagN
from table4 group by name ) SummaryTable
group by name
好象select第一张表的统计是正确的,可后面的表就不对了,(我试了两张表的连接情况)
and t0.name=t2.name (+)
左连接不会对(sum(decode(t2.flag,'Y',1,0)) table2Y)等列补空值
from
(
select t1.name,t1.flag,1 as t_id
from t1
union all
select t2.name,t2.flag,2 as t_id
from t2
union all
select t3.name,t3.flag,3 as t_id
from t3
union all
select t4.name,t4.flag,4 as t_id
from t4
) t
group by t.t_id,t.flag,t.name查询结果:
rowid t_id flag name count(*)
1 1 n ccc 1
2 1 n fff 1
3 1 y aaa 2
4 1 y ccc 2
5 1 y ddd 1
6 1 y fff 3
7 2 n fff 1
8 2 y aaa 1
9 2 y fff 3
10 3 y ccc 3
11 4 n fff 1
12 4 y aaa 1
13 4 y ggg 1