表中有三条数据：
日期 值
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怎么写？谢谢～～
------解决方案--------------------
- SQL code
SQL> select * from t_get_serial; TIME VALUE----------- ----------2010-12-1 12010-12-5 22010-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 12010-12-2 12010-12-3 12010-12-4 12010-12-5 22010-12-6 22010-12-7 22010-12-8 22010-12-9 32010-12-10 3 10 rows selected SQL>
------解决方案--------------------
- SQL code
--想了下，用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-rqorder by 1RQ VAL----------- ------------2010-12-01 12010-12-02 12010-12-03 12010-12-04 12010-12-05 22010-12-06 22010-12-07 22010-12-08 22010-12-09 3
------解决方案--------------------
- SQL code
with temp as(select to_date('2010-12-01','yyyy-mm-dd') rq,1 num from dualunion allselect to_date('2010-12-05','yyyy-mm-dd') rq,2 num from dualunion allselect 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-1order by rq+lv-1