WITH a AS
(
SELECT 'TV' item, 2 qty, 'A' loc FROM dual
UNION ALL SELECT 'TV', 5, 'B' FROM dual
UNION ALL SELECT 'TV', 4, 'C' FROM dual
)
, b AS
(
SELECT 'TV' item, 10 qty FROM dual
)
SELECT item
     , b_qty
     , a_qty
     , LEAST(a_qty, b_qty - sum_qty + a_qty) c_qty
     , loc
  FROM (
        SELECT b.item
             , b.qty b_qty
             , a.qty a_qty
             , a.loc
             , SUM(a.qty) OVER(PARTITION BY a.item ORDER BY a.loc) sum_qty
          FROM a, b
         WHERE a.item = b.item
        )
 WHERE b_qty > sum_qty - a_qty
;

아...감탄~~~

감사합니다. ^^