依然通过SUM OVER,只不过和累计和相比,这次加了一层select,通过row_number() over()来判定每次加的数值的符号,只有第一个数值是正号,其他的都取其相反数。
SQL> select empno,ename,sal,
2 sum(case when rn=1 then sal else -sal end) over(order by sal) as running_diff
3 from
4 (
5 select empno,ename,sal,row_number() over(order by sal) rn
6 from emp
7 where deptno=10
8 );
EMPNO ENAME SAL RUNNING_DIFF
---------- ---------- ---------- ------------
7934 MILLER 1300 1300
7782 CLARK 2450 -1150
7839 KING 5000 -6150