WITH TB AS (
SELECT  'A001' AS PNO, 4 AS AMT FROM DUAL UNION ALL 
SELECT 'A001' AS PNO, 4 AS AMT FROM DUAL UNION ALL 
SELECT 'A001' AS PNO, 4 AS AMT FROM DUAL UNION ALL 
select 'A001' as pno, 4 as amt from dual union all 
SELECT 'A001' AS PNO, 4 AS AMT FROM DUAL UNION ALL 
SELECT 'A001' AS PNO, 4 AS AMT FROM DUAL UNION ALL 
select  'A002' as pno, 8 as amt from dual 
)
SELECT A.*, A.SUM_AMT - 13 
FROM (
SELECT  A.*,
NVL(LAG(SUM_AMT) OVER(PARTITION BY PNO ORDER BY RN),0) AS CHECK_SUM  -- 합계를 만족시키는 최종 ROW확인을 위해 이전 ROW의 합산값을 가져옴
from (
SELECT 
A.* , 
SUM(AMT) OVER(PARTITION BY PNO ORDER BY PNO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SUM_AMT,
ROW_NUMBER() OVER(PARTITION BY PNO ORDER BY  PNO) AS RN -- 순서 구분을 위해 생성
FROM TB A 
) A
) A
WHERE /* 합계를 채우지 못한 대상도 출력 */ (A.CHECK_SUM - 13 < 0) OR 
/* 합계를 만족시키는 최종 ROW 출력  */ ( (A.CHECK_SUM - 13 )  < 0 AND (A.SUM_AMT - 13) >= 0)
--and pno = 'A001'
;

 

정렬 기준 항목이 필요한 문제입니다.

WITH t AS
(
SELECT 'A001' cd, 1 seq, 4 cnt FROM dual
UNION ALL SELECT 'A001', 2, 4 FROM dual
UNION ALL SELECT 'A001', 3, 4 FROM dual
UNION ALL SELECT 'A001', 4, 4 FROM dual
UNION ALL SELECT 'A001', 5, 4 FROM dual
)
SELECT cd, seq, cnt
     , GREATEST(sum_cnt - v_cnt, 0) cnt_rem
  FROM (SELECT cd, seq, cnt
             , SUM(cnt) OVER(PARTITION BY cd ORDER BY seq) sum_cnt
             , 13 v_cnt
          FROM t
         WHERE cd = 'A001'
        )
 WHERE sum_cnt - cnt < v_cnt
;