如何将某一时间段的记录分解成多条?
有这样一个表(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,多谢了,在线等!
------解决方案--------------------我测试是可以的,你试试看~~~
========================第一种========================
SQL> select tt.xmmc,
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 ')