17,089
社区成员
发帖
与我相关
我的任务
分享
with tt1 as(select 'A' name,'2008-02-03' rdate,'2008-04-03' rodate,2 price1,'2008-02-03' vdate,'2008-04003' vodate,1 price2 from dual
union all select 'A','2008-04-03','2008-05-23',4,'2008-04-03',null,3 from dual
union all select 'A','2008-05-23','2008-06-04',6,null,null,null from dual
union all select 'A','2008-06-03',null,8,null,null,null from dual)
,tt2 as(select 'A' name,'2008-01-01' pdate,null podate,13 price3 from dual)
select * from(
select tt1.name,tt1.rodate,decode(tt1.price1,null,lag(tt1.price1)over(order by
case when tt1.rdate<='2008-05-25' and (tt1.rodate>'2008-05-25' or tt1.rodate is null) then '2008-05-25' when tt1.price1 is null then '2020' else tt1.rdate end ),tt1.price1)price1,
decode(tt1.price2,null,lag(tt1.price2)over(order by
case when tt1.rdate<='2008-05-25' and (tt1.rodate>'2008-05-25' or tt1.rodate is null) then '2008-05-25' when tt1.price2 is null then '2020' else tt1.rdate end ),tt1.price2)price2
,tt2.price3
from tt1,tt2
where tt1.name=tt2.name
and tt1.rdate<='2008-05-25')
where rodate>'2008-05-25'