[Oracle] 선입선출 Or 순차적 차감 SQL

[Oracle] 선입선출 Or 순차적 차감 SQL

- 예 : 리스트에서 해당 숫자(여기에선 700)을 위에서부터 차감

WITH GROUP_QT AS ( SELECT 1 Seq, 100 Amt FROM DUAL UNION ALL SELECT 2 Seq, 200 Amt FROM DUAL UNION ALL SELECT 3 Seq, 300 Amt FROM DUAL UNION ALL SELECT 4 Seq, 400 Amt FROM DUAL ) SELECT A.Seq,A.Amt, CASE WHEN SUM(B.Amt)-700<0 THEN A.Amt ELSE A.Amt-(SUM(B.Amt)-700) END Use_Amt FROM GROUP_QT A LEFT JOIN GROUP_QT B ON B.Seq <= A.Seq GROUP BY A.Seq, A.Amt HAVING A.Amt-SUM(B.Amt) > 700*(-1);