oracle 月度统计
如下面这张表：
paydate charge
2011-4-11 800
2011-4-15 200
2011-5-6 300
2011-5-12 700
2012-4-12 500
2012-4-13 600
2012-4-17 700
2012-5-1 300
2012-5-3 800
现需要按月统计，大致结果如下
04 05
2011 1000 1000
2012 1800 1100
请问，这个SQL该怎么写。。。。。。。。。头都想破了也写不出来。。。。。
[解决办法]
一般这种情况 是一个月一个月拼出来的
[解决办法]
--反正一年也就12个月，都写一次CASE WHEN就行。
--paydate charge
with t as (
select date'2011-4-11' as paydate, 800 as charge from dual
union all
select date'2011-4-15', 200 from dual
union all
select date' 2011-5-6', 300 from dual
union all
select date' 2011-5-12', 700 from dual
union all
select date' 2012-4-12', 500 from dual
union all
select date' 2012-4-13', 600 from dual
union all
select date' 2012-4-17', 700 from dual
union all
select date' 2012-5-1', 300 from dual
union all
select date' 2012-5-3', 800 from dual
)
select payyear,
sum(case when paymonth = '04' then charge else 0 end) as "04" ,
sum(case when paymonth = '05' then charge else 0 end) as "05"
from (
select to_char(paydate,'yyyy') as payyear,to_char(paydate,'mm') as paymonth,charge from t
) ta
group by payyear
[解决办法]
如果时间是date类型
select to_char(paydate,'yyyy') year,
sum(case extract(month from paydate) when 4 then charge end) "04",
sum(case extract(month from paydate) when 5 then charge end) "05"
from t
group by to_char(paydate,'yyyy');
