Oracle Sql 求教——两表联合查询统计问题

select custem, sum(isnull(money,0)) as money,
sum(isnull(money1,0)) as money1,
sum(isnull(money2,0)) as money2,
sum(isnull(money3,0)) as money3
from
(select B.custem, money
case when state = 1 then money else 0 end money1,
case when state = 2 then money else 0 end money2,
case when state = 3 then money else 0 end money3
from A,B
where A.con_no=B.con_no )
group by custem

================================================
select custem, sum(isnull(money,0)) as money, sum(cnt) as cnt
sum(isnull(money1,0)) as money1,
sum(isnull(cnt1,0)) as cnt1,
sum(isnull(money2,0)) as money2,
sum(isnull(cnt2,0)) as cnt2,
sum(isnull(money3,0)) as money3 ,
sum(isnull(cnt3,0)) as cnt3,
from
(select B.custem, money , 1 as cnt
case when state = 1 then money else 0 end money1,
case when state = 1 then 1 else 0 end cnt1,

case when state = 2 then money else 0 end money2,
case when state = 2 then 1 else 0 end cnt2,
case when state = 3 then money else 0 end money3 ,
case when state = 3 then 1 else 0 end cnt3
from A,B
where A.con_no=B.con_no )
group by custem

select custem,sum(case state when 1 then money) money1,
sum(case state when 2 then money) money2,
sum(case state when 3 then money) money3
from A,B
where A.con_no=B.con_no