如何将某一时间段的记录分解成多条？
有这样一个表（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 ') 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 250
7 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 250
7 rows selected
