BEG_DATE MTM CHAN
2007-01-01 0.00 123.00
2007-01-02 120.00 220.00
2007-01-04 310.00 230.00
2007-02-01 0.00 1000.00
2007-02-02 100.00 110有如上表,现在的结果是想要当当前日期的MTM为0时,用下一天的CHAN替代当天的MTM,如2007-01-01的MTM=0,那就将2007-01-02的CHAN=220替代2007-01-01的MTM。可以生成另外一列MTM2需要得到的结果表是:
BEG_DATE MTM CHAN MTM2
2007-01-01 0.00 123.00 220
2007-01-02 120.00 220.00 120
2007-01-04 310.00 230.00 310
2007-02-01 0.00 1000.00 110
2007-02-02 100.00 110 100高手们,这语句怎么写啊。在线等
2007-01-01 0.00 123.00
2007-01-02 120.00 220.00
2007-01-04 310.00 230.00
2007-02-01 0.00 1000.00
2007-02-02 100.00 110有如上表,现在的结果是想要当当前日期的MTM为0时,用下一天的CHAN替代当天的MTM,如2007-01-01的MTM=0,那就将2007-01-02的CHAN=220替代2007-01-01的MTM。可以生成另外一列MTM2需要得到的结果表是:
BEG_DATE MTM CHAN MTM2
2007-01-01 0.00 123.00 220
2007-01-02 120.00 220.00 120
2007-01-04 310.00 230.00 310
2007-02-01 0.00 1000.00 110
2007-02-02 100.00 110 100高手们,这语句怎么写啊。在线等
BEG_DATE MTM CHAN
2007-01-01 0.00 123.00
2007-01-02 120.00 220.00
2007-01-04 310.00 230.00
2007-02-01 0.00 1000.00
2007-02-02 100.00 110
有如上表,现在的结果是想要当当前日期的MTM为0时,用下一天的CHAN替代当天的MTM,如2007-01-01的MTM=0,那就将2007-01-02的CHAN=220替代2007-01-01的MTM。可以生成另外一列MTM2
希望的结果表:BEG_DATE MTM CHAN MTM2
2007-01-01 0.00 123.00 220
2007-01-02 120.00 220.00 120
2007-01-04 310.00 230.00 310
2007-02-01 0.00 1000.00 110
2007-02-02 100.00 110 100
case when lag(MTM,1,null) over(order by userid) =0 then else MTM end as MTM2
from 原表
lag函数
case when lag(MTM,1,null) over(order by BEG_DATE) =0 then else MTM end as MTM2
from 原表
case when MTM=0 then lag(MTM,1,null) over(order by BEG_DATE) else MTM end as MTM2
from 原表
SQL> create table tab4(
2 beg_date date,
3 mtm number,
4 chan number
5 );Table createdSQL> insert into tab4
2 select to_date('2007-01-01','yyyy-mm-dd'),0.00,123.00 from dual
3 union
4 select to_date('2007-01-02','yyyy-mm-dd'),120.00,220.00 from dual
5 union
6 select to_date('2007-01-04','yyyy-mm-dd'),310.00,230.00 from dual
7 union
8 select to_date('2007-02-01','yyyy-mm-dd'),0.00,1000.00 from dual
9 union
10 select to_date('2007-02-02','yyyy-mm-dd'),100.00,110 from dual;5 rows insertedSQL> select BEG_DATE,MTM,CHAN,decode(mtm,0,(select chan from tab4 where beg_date=A.beg_date+1),MTM) mtm2 from tab4 A;BEG_DATE MTM CHAN MTM2
----------- ---------- ---------- ----------
01/01/2007 0 123 220
02/01/2007 120 220 120
04/01/2007 310 230 310
01/02/2007 0 1000 110
02/02/2007 100 110 100
case when MTM=0 then lag(chan,1,null)over(order by BEG_DATE desc) else MTM end as MTM2
from tab4 order by BEG_DATE 测试过了,借用楼上的数据
前面问题是需要取的是chan,我取的是MTM
还有就是over(order by BEG_DATE desc)