create table a (id int,i int);
insert into a values(1,2);
insert into a values(2,7);
insert into a values(3,19);
insert into a values(4,48);
insert into a values(5,100);
commit;SQL> select * from a; ID I
---------- ----------
1 2
2 7
3 19
4 48
5 100
现在要求一个值:本身的值与比它小ID值的累加想减
结果集为 ID I value
---------- ---------- ----------
1 2 2
2 7 5
3 19 10
4 48 20
5 100 24求相应SQL语句
from tb a,
(select (id-1) id, sum(i) over(order by id) i from tb)b
where a.id=b.id
select a.*,(a.i-b.i) value
from tb a
left join (select (id+1) id, sum(i) over(order by id) i from tb)b
on a.id=b.id
SELECT ID, I-NVL(LAG(I) OVER(ORDER BY ID),0)AS I FROM A