데이터베이스 질문과 답변
안녕하세요.
푹푹찌는 무더위입니다 ㅠㅠ
제 머리도 푹푹찌네요..
원하는(특정) 수량만큼 대상 테이블의 SEQ 순으로 수량의 합이 맞는 데이타만 보여지고 싶어 이렇게 문의 합니다
제가 말을 못써서 하기 데이타 보시면 이해가 빠르실겁니다..
[A1 테이블]
SELECT 'A' Main, 800 Tot_Qty from Dual
[B1 테이블]
SELECT 'A' Main, 1 seq, 100 QTY FROM dual
UNION ALL SELECT 'A', 2, 100 FROM dual
UNION ALL SELECT 'A', 3, 100 FROM dual
UNION ALL SELECT 'A', 4, 200 FROM dual
UNION ALL SELECT 'A', 5, 300 FROM dual
UNION ALL SELECT 'A', 6, 150 FROM dual
UNION ALL SELECT 'A', 7, 250 FROM dual
UNION ALL SELECT 'A', 8, 100 FROM dual
UNION ALL SELECT 'A', 9, 400 FROM dual
UNION ALL SELECT 'A', 10, 100 FROM dual
UNION ALL SELECT 'A', 11, 2000 FROM dual
[결과]
합이 800이 되는 SEQ 1~5 까지만 보여지기
SELECT 'A' Main, 1 seq, 100 QTY FROM dual
UNION ALL SELECT 'A', 2, 100 FROM dual
UNION ALL SELECT 'A', 3, 100 FROM dual
UNION ALL SELECT 'A', 4, 200 FROM dual
UNION ALL SELECT 'A', 5, 300 FROM dual
부탁드립니다!
WITH T AS (
SELECT 'A' MAIN, 1 SEQ, 100 QTY FROM DUAL
UNION ALL SELECT 'A', 2, 100 FROM DUAL
UNION ALL SELECT 'A', 3, 100 FROM DUAL
UNION ALL SELECT 'A', 4, 200 FROM DUAL
UNION ALL SELECT 'A', 5, 300 FROM DUAL
UNION ALL SELECT 'A', 6, 150 FROM DUAL
UNION ALL SELECT 'A', 7, 250 FROM DUAL
UNION ALL SELECT 'A', 8, 100 FROM DUAL
UNION ALL SELECT 'A', 9, 400 FROM DUAL
UNION ALL SELECT 'A', 10, 100 FROM DUAL
UNION ALL SELECT 'A', 11, 2000 FROM DUAL)
SELECT A1.*
FROM (SELECT MAIN
, SEQ
, QTY
, SUM( QTY ) OVER( PARTITION BY MAIN ORDER BY SEQ ASC ) TOT_QTY
FROM T) A1
, (SELECT 'A' MAIN, 800 TOT_QTY FROM DUAL ) B1
WHERE A1.MAIN = B1.MAIN
AND A1.TOT_QTY <= B1.TOT_QTY
WITH A AS ( SELECT 'A' Main, 800 Tot_Qty from Dual ) , B AS ( SELECT 'A' Main, 1 seq, 100 QTY FROM dual UNION ALL SELECT 'A', 2, 100 FROM dual UNION ALL SELECT 'A', 3, 100 FROM dual UNION ALL SELECT 'A', 4, 200 FROM dual UNION ALL SELECT 'A', 5, 300 FROM dual UNION ALL SELECT 'A', 6, 150 FROM dual UNION ALL SELECT 'A', 7, 250 FROM dual UNION ALL SELECT 'A', 8, 100 FROM dual UNION ALL SELECT 'A', 9, 400 FROM dual UNION ALL SELECT 'A', 10, 100 FROM dual UNION ALL SELECT 'A', 11, 2000 FROM dual ) SELECT * FROM (SELECT MAIN , SEQ , QTY , SUM(QTY) OVER(ORDER BY SEQ ) TOT_QTY FROM B ) WHERE TOT_QTY <= (SELECT TOT_QTY FROM A )
합계가 딱 맞아 떨어지지 않는 경우를 감안해야 할 듯 합니다.
WITH a1 AS
(
SELECT 'A' main, 700 tot_qty FROM dual
)
, b1 AS
(
SELECT 'A' main, 1 seq, 100 qty FROM dual
UNION ALL SELECT 'A', 2, 100 FROM dual
UNION ALL SELECT 'A', 3, 100 FROM dual
UNION ALL SELECT 'A', 4, 200 FROM dual
UNION ALL SELECT 'A', 5, 300 FROM dual
UNION ALL SELECT 'A', 6, 150 FROM dual
UNION ALL SELECT 'A', 7, 250 FROM dual
UNION ALL SELECT 'A', 8, 100 FROM dual
UNION ALL SELECT 'A', 9, 400 FROM dual
UNION ALL SELECT 'A', 10, 100 FROM dual
UNION ALL SELECT 'A', 11, 2000 FROM dual
)
SELECT a.main
, a.tot_qty
, b.seq
, b.qty
, LEAST(b.sum_qty, a.tot_qty) - b.sum_qty + b.qty AS use_qty
FROM a1 a
, (SELECT main, seq, qty
, SUM(qty) OVER(PARTITION BY main ORDER BY seq) sum_qty
FROM b1
) b
WHERE b.main = a.main
AND b.sum_qty - b.qty < a.tot_qty
;
) 버튼을 클릭하여 작성 하시면 됩니다.