为表增加“某列值与上一行同列值差”的列，写sql怎么完成呢？

Question

本帖最后由 happyhhb 于 2011-12-22 08:38:30 编辑

Answer 1

select id,f1,f2,lag(f2,1,f2)over(order by id)-f2 f3
  from t1;

Answer 2

create table t1(id number,f1 number,f2 number);insert into t1
SELECT 1,4,3 FROM dual
UNION ALL
SELECT 2,5,4 from dual
UNION ALL
SELECT 3,2,7 from dual
UNION ALL
SELECT 4,3,8 from dual;
SELECT aa.ID,aa.f1,aa.f2,nvl(aa.f2-bb.f2,0) f3
FROM t1 aa
LEFT JOIN (SELECT ROWNUM+1 num,ID,f1,f2 FROM t1) bb
ON aa.ID=bb.num
order by aa.id asc;/*
ID                     F1                     F2                     F3                     
---------------------- ---------------------- ---------------------- ---------------------- 
1                      4                      3                      0                      
2                      5                      4                      1                      
3                      2                      7                      3                      
4                      3                      8                      1                      */