Oracle中按日期展开获取数据

[happyoneeve]

表中有三条数据：
日期 值
2010-12-01 1
2010-12-05 2
2010-12-09 3
想获得2010-12-01到12-10的结果：
2010-12-01 1
2010-12-02 1
2010-12-03 1
2010-12-04 1
2010-12-05 2
2010-12-06 2
2010-12-07 2
2010-12-08 2
2010-12-09 3
2010-12-10 3
的SQL怎么写？谢谢～～

[minitoy]

SQL> select * from t_get_serial;
 
TIME             VALUE
----------- ----------
2010-12-1            1
2010-12-5            2
2010-12-9            3
 
SQL> select a.stat_time,max(b.value) from
  2  (select to_date('2010-12-01','yyyy-mm-dd')+rownum-1 stat_time from dual connect by rownum<=10)a,t_get_serial b
  3  where b.time<=a.stat_time
  4  group by a.stat_time
  5  order by a.stat_time;
 
STAT_TIME   MAX(B.VALUE)
----------- ------------
2010-12-1              1
2010-12-2              1
2010-12-3              1
2010-12-4              1
2010-12-5              2
2010-12-6              2
2010-12-7              2
2010-12-8              2
2010-12-9              3
2010-12-10             3
 
10 rows selected
 
SQL> 

[gelyon]

--想了下，用connect by来做，但是你要的结果数据不对称，会少掉最后一个
--minitoy方法不错，根据中间表来连接

select distinct rq+level-1 as rq,val from(
select rq,lead(rq,1,rq+1)over(order by rq) lag_rq, val from tab
)
connect by level<=lag_rq-rq
order by 1

RQ          VAL
----------- ------------
2010-12-01         1
2010-12-02         1
2010-12-03         1
2010-12-04         1
2010-12-05         2
2010-12-06         2
2010-12-07         2
2010-12-08         2
2010-12-09         3

[gelyon]

--oh,可以，lead(rq,1,rq+2)就行了
select distinct rq+level-1 as rq,val from(
select rq,lead(rq,1,rq+2)over(order by rq) lag_rq, val from tab
)
connect by level<=lag_rq-rq
order by 1

[Phoenix_99]

with temp as(
select  to_date('2010-12-01','yyyy-mm-dd') rq,1 num from dual
union all
select  to_date('2010-12-05','yyyy-mm-dd') rq,2 num from dual
union all
select  to_date('2010-12-09','yyyy-mm-dd') rq,3 num from dual
),t as(
select level lv from dual connect by level < 32
)

select max(rq+lv-1) rq1,max(num) num1 from temp,t 
where rq+lv -1 < to_date('2010-12-11','yyyy-mm-dd')
group by rq+lv-1
order by rq+lv-1

[chen825808455]

学习了 ！！！