如下面这张表:
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该怎么写头都想破了也写不出来。。
解决方案 »
- oracle 包含查询
- oracle 创建临时表,用过一次如何删除,不然ORA-00955: 名称已由现有对象使用
- Linux ES4 下 Oracle9i 装后,如何让系统启动时,自动运行.
- 临时表的序列号可以自动增加吗
- OCCI程序运行时出现ORA-12705错误如何解决
- 怎么找出和某个表关联的其它表的名字呢
- 数据库无备份,系统崩溃了,有无办法恢复数据?
- oracle中同义词是什么意思啊?多谢!
- ORACLE数据库能否存取word、excel类型的文档?
- 有没有把mssql的存储过程转换为oracle存储过程的工具?
- ora-01036 非法的变量名/编号???????测试不通过
- 求一程序包,用于记录脚本耗时,详细需求如下:
--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
------- ---------------------- ----------------------
2011 1000 1000
2012 1800 1100
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');
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);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'),'04',charge,0)) c5
--...
from t1
group by to_char(paydate,'yyyy')
yy c1 c4 c5
-----------------------------------------
1 2011 0 1000 1000
2 2012 0 1200 1200