问oracle在查询中使用上述单元格计算值。

EN

CREATE TABLE T
(SORT_KEY   NUMBER,
 EMP_NO     NUMBER,
 SAL        NUMBER,
 DEDUCTION  NUMBER);

INSERT INTO T VALUES(1, 1, 10, 5);
INSERT INTO T VALUES(2, 1, 10, 2);
INSERT INTO T VALUES(3, 1, 15, 2);
INSERT INTO T VALUES(4, 1, 21, 1);

COMMIT;

SELECT SORT_KEY, SAL,
       ABS(V) AS DEDUCTION,
       SUM(V) OVER(ORDER BY SORT_KEY) AS BALANCE
FROM (SELECT SORT_KEY, SAL,
             CASE WHEN LAG(DEDUCTION, 1) OVER(ORDER BY SORT_KEY) IS NULL
                  THEN SAL - DEDUCTION
                  ELSE - DEDUCTION END AS V
      FROM t
      ORDER BY SORT_KEY)
ORDER BY SORT_KEY;

SORT_KEY   SAL   DEDUCTION  BALANCE
--------   --    ---------  -------
       1   10    5          5
       2   10    2          3
       3   15    2          1
       4   21    1          0

DROP TABLE T PURGE;

INSERT INTO T VALUES(5, 2, 10, 5);
INSERT INTO T VALUES(6, 2, 10, 2);
INSERT INTO T VALUES(7, 2, 15, 2);
INSERT INTO T VALUES(8, 2, 21, 1);

COMMIT;

SELECT SORT_KEY, EMP_NO, SAL,
       ABS(V) AS DEDUCTION,
       SUM(V) OVER(PARTITION BY EMP_NO ORDER BY SORT_KEY) AS BALANCE
FROM (SELECT SORT_KEY, EMP_NO, SAL,
             CASE WHEN LAG(DEDUCTION, 1) OVER(PARTITION BY EMP_NO ORDER BY SORT_KEY) IS NULL
                  THEN SAL - DEDUCTION
                  ELSE - DEDUCTION END AS V
      FROM t
      ORDER BY SORT_KEY)
ORDER BY EMP_NO, SORT_KEY;

with employees as (
  select 1 as rn, 1 as emp_no,10 as sal,5 as deduction, '10-5=5' as balance from dual union all 
  select 2 as rn, 1 as emp_no,10 as sal,2 as deduction, '5-2=3' as balance from dual union all 
  select 3 as rn, 1 as emp_no,15 as sal,2 as deduction, '3-2=1' as balance from dual union all 
  select 4 as rn, 1 as emp_no,21 as sal,1 as deduction, '1-1=0' as balance from dual )
select v1.*, 
  v1.sal - v1.aggr_deduction as remaining_sal_current_sal,
  v1.first_sal - v1.aggr_deduction as remaining_sal_first_sal 
from (
select rn,
       emp_no, 
       sal, 
       deduction,
       balance,
       sum(deduction) over (partition by emp_no order by rn) as aggr_deduction,
       (select sal from employees e1 where rn = 1 ) as first_sal
from employees
) v1
order by rn