有这样一个表(table1),里面的记录如下
xmmc kssj jssj dliang
项目1 2007-01-01 2007-03-31 300
项目2 2007-02-01 2007-05-31 1000
我现在要上面的记录按月份分解,将这个时间范围内的总的电量按月平均,也就是说这个时间段内有多少个月,那我下面这个表的平均电量(yuedliang)就为这个时间段的总电量除于算出来的月的数量,结果如下:
xmmc kssj jssj yuefen yuedliang
项目1 2007-01-01 2007-07-31 2007-01 100
项目1 2007-01-01 2007-07-31 2007-02 100
项目1 2007-01-01 2007-07-31 2007-03 100
项目2 2007-02-01 2007-05-31 2007-02 200
项目2 2007-02-01 2007-05-31 2007-03 200
项目2 2007-02-01 2007-05-31 2007-04 200
项目2 2007-02-01 2007-05-31 2007-05 200
不知道用sql语句能否实现,不行写个存储过程也ok,多谢了,在线等!
xmmc kssj jssj dliang
项目1 2007-01-01 2007-03-31 300
项目2 2007-02-01 2007-05-31 1000
我现在要上面的记录按月份分解,将这个时间范围内的总的电量按月平均,也就是说这个时间段内有多少个月,那我下面这个表的平均电量(yuedliang)就为这个时间段的总电量除于算出来的月的数量,结果如下:
xmmc kssj jssj yuefen yuedliang
项目1 2007-01-01 2007-07-31 2007-01 100
项目1 2007-01-01 2007-07-31 2007-02 100
项目1 2007-01-01 2007-07-31 2007-03 100
项目2 2007-02-01 2007-05-31 2007-02 200
项目2 2007-02-01 2007-05-31 2007-03 200
项目2 2007-02-01 2007-05-31 2007-04 200
项目2 2007-02-01 2007-05-31 2007-05 200
不知道用sql语句能否实现,不行写个存储过程也ok,多谢了,在线等!
解决方案 »
- 这个应该怎么写 select max(3,4,5,6) from dual
- 使用OCI 如何获取OCIStmtExecute 执行select语句后的记录数的行数
- 急!求助 ora-1122 can not open database!!!!
- 急急急!....求一个Oracle语句.
- oracle快照问题
- 各位大虾,看看这个触发器怎么写?
- 怎样用程序实现在线备份oracle数据库?
- oracle 的安装问题!急,急,急.....,在线等待
- 过程里面不能创建表对象吗?
- sql loader 导入数据,加上最后一列就无法正常导入
- 求助:ORA-01031
- 刚接触Oracle几天,,问个超级菜鸟问题
2 add_months(tt.kssj,rr-1) as kssj,
3 decode(sign(add_months(tt.kssj,rr)-tt.jssj),1,tt.jssj,add_months(tt.kssj,rr)) as jssj,
4 round(tt.dliang/(to_number(months_between(tt.jssj+1,tt.kssj)))) as avg_dliang
5 from (
6 select '项目1' as xmmc,to_date('2007-01-01','yyyy-mm-dd') as kssj,to_date('2007-03-31','yyyy-mm-dd') as jssj,300 as dliang from dual
7 union all
8 select '项目2' as xmmc,to_date('2007-02-01','yyyy-mm-dd') as kssj,to_date('2007-05-31','yyyy-mm-dd') as jssj,1000 as dliang from dual
9 )tt,
10 (
11 select rownum rr from all_objects where rownum<1000
12 )
13 where add_months(tt.kssj,rr)-(tt.jssj+1)<=0;XMMC KSSJ JSSJ AVG_DLIANG
--------- ----------- ----------- ----------
项目1 1/1/2007 2/1/2007 100
项目1 2/1/2007 3/1/2007 100
项目1 3/1/2007 3/31/2007 100
项目2 2/1/2007 3/1/2007 250
项目2 3/1/2007 4/1/2007 250
项目2 4/1/2007 5/1/2007 250
项目2 5/1/2007 5/31/2007 2507 rows selected
==========================第二种==================SQL> select tt.xmmc,
2 tt.kssj,
3 tt.jssj,
4 to_char(decode(sign(add_months(tt.kssj,rr)-tt.jssj),1,tt.jssj,add_months(tt.kssj,rr-1)),'yyyy-mm') as jssj,
5 round(tt.dliang/(to_number(months_between(tt.jssj+1,tt.kssj)))) as avg_dliang
6 from (
7 select '项目1' as xmmc,to_date('2007-01-01','yyyy-mm-dd') as kssj,to_date('2007-03-31','yyyy-mm-dd') as jssj,300 as dliang from dual
8 union all
9 select '项目2' as xmmc,to_date('2007-02-01','yyyy-mm-dd') as kssj,to_date('2007-05-31','yyyy-mm-dd') as jssj,1000 as dliang from dual
10 )tt,
11 (
12 select rownum rr from all_objects where rownum<1000
13 )
14 where add_months(tt.kssj,rr)-(tt.jssj+1)<=0;XMMC KSSJ JSSJ JSSJ AVG_DLIANG
--------- ----------- ----------- ------- ----------
项目1 1/1/2007 3/31/2007 2007-01 100
项目1 1/1/2007 3/31/2007 2007-02 100
项目1 1/1/2007 3/31/2007 2007-03 100
项目2 2/1/2007 5/31/2007 2007-02 250
项目2 2/1/2007 5/31/2007 2007-03 250
项目2 2/1/2007 5/31/2007 2007-04 250
项目2 2/1/2007 5/31/2007 2007-05 2507 rows selected