日期:2014-05-17 浏览次数:20908 次
WITH test AS (
SELECT 12 DAY,10 a,0 b FROM dual UNION ALL
SELECT 11,8,NULL FROM dual )
SELECT DAY,
A,
b,
(0.2 *
(SELECT DECODE(B, NULL,10, B) FROM TEST K WHERE K.DAY = T.DAY - 1) +
0.8 * A)
FROM TEST T
WITH test AS (
SELECT 12 DAY,10 a,0 b FROM dual UNION ALL
SELECT 11,8,NULL FROM dual UNION ALL
SELECT 13,8,8 FROM dual)
SELECT DAY,
A,
B,
(0.2 *
DECODE((SELECT B FROM TEST K WHERE K.DAY = T.DAY - 1),
NULL,
10,
(SELECT B FROM TEST K WHERE K.DAY = T.DAY - 1)) + 0.8 * A)
FROM TEST T
SQL> create table tt_test (tday date,a number,b number);
Table created
SQL>
SQL> insert into tt_test (tday,a) values (to_date('2014/03/01','yyyy/mm/dd'),5);
1 row inserted
SQL> insert into tt_test (tday,a) values (to_date('2014/03/02','yyyy/mm/dd'),10);
1 row inserted
SQL> insert into tt_test (tday,a) values (to_date('2014/03/03','yyyy/mm/dd'),15);
1 row inserted
SQL> insert into tt_test (tday,a) values (to_date('2014/03/05','yyyy/mm/dd'),20);
1 row inserted
SQL> commit;
Commit complete
SQL>
SQL> update tt_test t1
2 set b = 1 / 5 *
3 decode((select b from tt_test where tday = t1.tday - 1),
4 null,
5 10,
6 (select b from tt_test