Question

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

设有表：
A [ id , con_no , money , state ] id为主键, con_no可能重复出现
B [ con_no , custem ] con_no为主键，不同的con_no可能对应相同的custem

需要统计以下信息（同一个custem输出一条统计信息）
----------------------------------------------------------------
custem money money[state=1] money[state=2] money[state=3]
----------------------------------------------------------------

求SQL
根据 tj_angela 和 mkim1980 两位老兄的答案，我用下面这个sql成功了
select custem_name,
sum(case when 1=1 then money else '0' end) money,
sum(case when state = '1' then money else '0' end) money1,
sum(case when state = '2' then money else '0' end) money2,
sum(case when state = '3' then money else '0' end) money3
from A,B
where A.con_no=B.con_no
group by custem_name

但如果我需要以下样式的统计，该怎么一一获取各状态对应的记录条数呢？（）

----------------------------------------------------------------
custem total[state=1] money[state=1] total[state=2] money[state=2] total[state=3] money[state=3]
----------------------------------------------------------------

Answer 1

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

Answer 2

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