例如一个表有3列
FROM_DATE | TO_DATE | DATA
2013-09-01 2013-09-07 100
2013-09-08 2013-09-30 200
如何可以转换成
DATE |DATA
2013-09-01 100
2013-09-02 100
2013-09-03 100
2013-09-04 100
2013-09-05 100
2013-09-06 100
2013-09-07 100
2013-09-08 200
.
.
.
2013-09-30 200
FROM_DATE | TO_DATE | DATA
2013-09-01 2013-09-07 100
2013-09-08 2013-09-30 200
如何可以转换成
DATE |DATA
2013-09-01 100
2013-09-02 100
2013-09-03 100
2013-09-04 100
2013-09-05 100
2013-09-06 100
2013-09-07 100
2013-09-08 200
.
.
.
2013-09-30 200
with a as (
select to_date('2013-09-01', 'yyyy-mm-dd') fromdate,
to_date('2013-09-07', 'yyyy-mm-dd') todate,
100 data
from dual
union
select to_date('2013-09-08', 'yyyy-mm-dd') fromdate,
to_date('2013-09-30', 'yyyy-mm-dd') todate,
200 data
from dual
)select fromdate + rownum - gap - 1 c1, data
from (select a1.fromdate, a1.todate, a1.data, nvl(a2.gap,0) gap
from (select fromdate, todate, data, rownum - 1 rn from a) a1,
(select fromdate,
todate,
data,
todate - fromdate+1 gap,
rownum rn
from a) a2
where a1.rn = a2.rn(+)) temp
connect by fromdate + rownum - gap - 1 <= todate