表taba数据如下:
colA colB colC
a1 b1 1
a1 b2 2
a1 b3 4
a1 b4 7
我想用一个用一个sql查询查出如下数据:
colA colB colC
a1 b1 1
a1 b2 2
a1 null 3
a1 b3 4
a1 null 5
a1 null 6
a1 b4 7
a1 null 8
sql语句如下:
select nvl(t.cola,'a'),t.colb,lv from test t,(
select level as lv from dual connect by level <=8) b
where t.colc(+)=b.lv
----------------------------------------------------------------------------------------------
我要连续的8天数据,没有就选出来空的
a a 2008/09/09
a b 2008/09/10
a d 2008/09/12
选出来之后把中间没有日期的补上
a a 2008/09/09
a b 2008/09/10
a null 2008/09/11
a d 2008/09/12
sql语句如下:
select t.cola,t.colb,lv from taba t,(
select to_date('2008/09/09','yyyy/mm/dd')+level as lv from dual connect by level <=8) b
where t.colc(+)=b.lv order by lv