Oracle - 순차적으로 차감하기3

[ Oracle - 순차적으로 차감하기3 ]

 

WITH 재고테이블 AS ( SELECT 'PRO' product, 150 qty FROM dual ) , 출하테이블 AS ( SELECT '2010-06-20' dt,'PRO' product, 100 qty FROM dual UNION ALL SELECT '2010-06-24', 'PRO', 80 FROM dual UNION ALL SELECT '2010-06-28', 'PRO', 70 FROM dual ) SELECT b.dt, b.product, b.qty , a.qty - b.s_qty 재고 FROM 재고테이블 a, (SELECT dt, product, qty , SUM(qty) OVER(PARTITION BY product ORDER BY dt) s_qty FROM 출하테이블 ) b WHERE a.product(+) = b.product AND a.qty(+) > b.s_qty - b.qty ;