单条SQL语句实现复杂逻辑几例~~

newkid · 发表于 2008-7-4 02:37

例子四是可以用分析函数的：

SELECT tmp4.*
   ,NVL(DECODE(addvalue,0, LAST_VALUE(DECODE(addvalue,0,NULL,addvalue) IGNORE NULLS) OVER(ORDER BY adddate DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),addvalue),addvalue) AS new_value
  FROM tmp4
ORDER BY adddate
/

ADDDATE             ADDVALUE NEW_VALUE
-------------------- ---------- -------------
2007-03-01                   0 3,64
2007-03-02                   0 3,64
2007-03-05                3,64 3,64
2007-03-06                3,82 3,82
2007-03-07                   0 3,47
2007-03-08                3,47 3,47
2007-03-09                   0 4,01
2007-03-12                   0 4,01
2007-03-13                4,01 4,01
2007-03-14                4,21 4,21
2007-03-15                4,12 4,12
2007-03-16                   0 0
2007-03-17                   0 0

13 rows selected.

[ 本帖最后由 newkid 于 2008-7-4 04:16 编辑 ]