WITH t AS ( SELECT '001' empno, 1 seq, 70000 amt FROM dual UNION ALL SELECT '001', 2, 40000 FROM dual UNION ALL SELECT '002', 1, 100000 FROM dual UNION ALL SELECT '002', 2, 110000 FROM dual UNION ALL SELECT '002', 3, 100000 FROM dual UNION ALL SELECT '003', 1, 40000 FROM dual UNION ALL SELECT '003', 2, 50000 FROM dual UNION ALL SELECT '003', 3, 60000 FROM dual ) SELECT empno, seq, amt , amt - LEAST(GREATEST(100000 - s_amt, 0), amt) 소득공제금액 , ROUND(LEAST(GREATEST(100000 - s_amt, 0), amt) / 1.1) 세액공제금액 FROM (SELECT empno, seq, amt , SUM(amt) OVER(PARTITION BY empno ORDER BY seq) - amt s_amt FROM t ) ;