对同列求DETAL值

来之itpub帖子 http://www.itpub.net/thread-1853767-1-1.html

表数据

id  value 
1    99
2    102
3    110
4    115
5    120

期望结果：
id  value   detal
1    99      0
2    102    3
3    110    8
4    115    5 
5    120    5

 

方法一： last_value() over()

with t as
 (select 1 id, 99 value from dual
  union
  select 2, 102 from dual
  union
  select 3, 110 from dual
  union
  select 4, 115 from dual
  union
  select 5, 120 from dual
  union
  select 6, 131 from dual)
select id,
       value,
       nvl(value - last_value(value) over(order by id rows between unbounded preceding and 1 preceding), 0) detal
  from t

方法二：lag() over()

with t as
(select 1 id, 99 value from dual
union
select 2, 102 from dual
union
select 3, 110 from dual
union
select 4, 115 from dual
union
select 5, 120 from dual
union
select 6, 131 from dual)
select id,
value,
value - lag(value, 1, value) over(order by id) diff
from t

 

方法三：自连接 t left join t t1 on t.id=t1.id-1

 

with t as
(select 1 id, 99 value from dual
  union
  select 2, 102 from dual
  union
  select 3, 110 from dual
  union
  select 4, 115 from dual
  union
  select 5, 120 from dual
  union
  select 6, 131 from dual)
select t.*,case when t.value-t1.value is null then 0 else t.value-t1.value end as detal from t left join t t1 on t1.id=t.id-1 order by t.id;