如下面这张表:
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该怎么写头都想破了也写不出来。。
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该怎么写头都想破了也写不出来。。
create table t1 (paydate date,charge number(10));insert into t1 values (date'2011-04-11',800);
insert into t1 values (date'2011-04-15',200);
insert into t1 values (date'2011-05-06',300);
insert into t1 values (date'2011-05-12',700);
insert into t1 values (date'2012-04-12',500);
insert into t1 values (date'2012-04-17',700);
insert into t1 values (date'2012-05-01',300);
insert into t1 values (date' 2012-05-03',800);
insert into t1 values (date'2012-04-18',700);select to_char(paydate,'yyyy') yy,
sum(decode(to_char(paydate,'mm'),'01',charge,0)) c1,
--...
sum(decode(to_char(paydate,'mm'),'04',charge,0)) c4,
sum(decode(to_char(paydate,'mm'),'05',charge,0)) c5
--...
from t1
group by to_char(paydate,'yyyy')
yy c1 c4 c5
-----------------------------------------
1 2011 0 1000 1000
2 2012 0 1900 1100
with t as(
select to_date('2011-04-11','yyyy-mm-dd') paydate,800 charge from dual
union all
select to_date('2011-04-15','yyyy-mm-dd'),200 from dual
union all
select to_date('2011-05-06','yyyy-mm-dd'),300 from dual
union all
select to_date('2011-05-12','yyyy-mm-dd'),700 from dual
union all
select to_date('2012-04-12','yyyy-mm-dd'),500 from dual
union all
select to_date('2012-04-13','yyyy-mm-dd'),600 from dual
union all
select to_date('2012-04-17','yyyy-mm-dd'),700 from dual
union all
select to_date('2012-05-01','yyyy-mm-dd'),300 from dual
union all
select to_date('2012-05-03','yyyy-mm-dd'),800 from dual
)
select to_char(paydate, 'yyyy') "年份",
sum(decode(to_char(paydate, 'mm'), '04', charge, 0)) "4月份",
sum(decode(to_char(paydate, 'mm'), '05', charge, 0)) "5月份"
from t
group by to_char(paydate, 'yyyy') 年份 4月份 5月份
---- ---------- ----------
2011 1000 1000
2012 1800 1100
with t as(
select to_date('2011-04-11','yyyy-mm-dd') paydate,800 charge from dual
union all
select to_date('2011-04-15','yyyy-mm-dd'),200 from dual
union all
select to_date('2011-05-06','yyyy-mm-dd'),300 from dual
union all
select to_date('2011-05-12','yyyy-mm-dd'),700 from dual
union all
select to_date('2012-04-12','yyyy-mm-dd'),500 from dual
union all
select to_date('2012-04-13','yyyy-mm-dd'),600 from dual
union all
select to_date('2012-04-17','yyyy-mm-dd'),700 from dual
union all
select to_date('2012-05-01','yyyy-mm-dd'),300 from dual
union all
select to_date('2012-05-03','yyyy-mm-dd'),800 from dual
)
select to_char(paydate, 'yyyy') "年份",
sum(decode(to_char(paydate, 'mm'), '04', charge, 0)) "4月份",
sum(decode(to_char(paydate, 'mm'), '05', charge, 0)) "5月份"
from t
group by to_char(paydate, 'yyyy') 年份 4月份 5月份
---- ---------- ----------
2011 1000 1000
2012 1800 1100