问题是这样的,需求是需要统计出20111231和20121231每个金额段的信息。例如:客户A在20111231是4W元低于5W并且在20121231还是低于5W为3W那属于第一个区间,则如图客户数加1,那么资产变化则为-1W后面以此类推,我写了下sql,但是觉得这样比较麻烦,并且只能查询出一个区间段的。
select count(*) as 人数,sum(b.qian1-a.qian1) from (
select distinct(cusno) as ren1 ,sum(depmdbbal) as qian1 from plat where datadate = 20111231 and depmdbbal<50000 group by cusno ) as a join
(select distinct(cusno) as ren1 ,sum(depmdbbal) as qian1 from plat where datadate = 20121231 and depmdbbal<50000 group by cusno) as b on a.ren1=b.ren1
求高手指点,谢谢。报表sql
case when 金额 <50000 then '5W以下'
when 金额 >=50000 and 金额<=100000 then '5W-10W'
end 区间,
count(客户ID)
from table
group by 客户ID,case when 金额 <50000 then '5W以下'
case when 金额 >=50000 and 金额<=100000 then '5W-10W'
end 区间
count(decode(w.q, 1, 1, 0)), sum(decode(w.q, 1, nvl(w1.depmdbbal, 0) - w.depmdbbal , 0)),
count(decode(w.q, 2, 1, 0)), sum(decode(w.q, 2, nvl(w1.depmdbbal, 0) - w.depmdbbal , 0)),
count(decode(w.q, 3, 1, 0)), sum(decode(w.q, 3, nvl(w1.depmdbbal, 0) - w.depmdbbal , 0)),
count(decode(w.q, 4, 1, 0)), sum(decode(w.q, 4, nvl(w1.depmdbbal, 0) - w.depmdbbal , 0)),
count(decode(w.q, 5, 1, 0)), sum(decode(w.q, 5, nvl(w1.depmdbbal, 0) - w.depmdbbal , 0)),
count(decode(w.q, 6, 1, 0)), sum(decode(w.q, 6, nvl(w1.depmdbbal, 0) - w.depmdbbal , 0)),
count(decode(w.q, 7, 1, 0)), sum(decode(w.q, 7, nvl(w1.depmdbbal, 0) - w.depmdbbal , 0))
from
(select cusno, sum(depmdbbal) depmdbbal,
case when sum(depmdbbal) < 50000 then 1
when sum(depmdbbal) >= 50000 and sum(depmdbbal) < 200000 then 2
when sum(depmdbbal) >= 200000 and sum(depmdbbal) < 500000 then 3
when sum(depmdbbal) >= 500000 and sum(depmdbbal) < 2000000 then 4
when sum(depmdbbal) >= 2000000 and sum(depmdbbal) < 5000000 then 5
when sum(depmdbbal) >= 5000000 and sum(depmdbbal) < 8000000 then 6
else 7
end q
from qian1 t where t.datadate = 20111231
group by cusno) w,
(select select cusno, sum(depmdbbal) depmdbbal
case when sum(depmdbbal) < 50000 then 1
when sum(depmdbbal) >= 50000 and sum(depmdbbal) < 200000 then 2
when sum(depmdbbal) >= 200000 and sum(depmdbbal) < 500000 then 3
when sum(depmdbbal) >= 500000 and sum(depmdbbal) < 2000000 then 4
when sum(depmdbbal) >= 2000000 and sum(depmdbbal) < 5000000 then 5
when sum(depmdbbal) >= 5000000 and sum(depmdbbal) < 8000000 then 6
else 7
end q from qian1 t1 where t1.datadate = 20121231
group by cusno) w1
where w.cusno = w1.cusno(+)
group by w1.q
order by q