日期:2014-05-17 浏览次数:20918 次
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>
------解决方案--------------------
--想了下,用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
------解决方案--------------------
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