총 수량만큼 합이 되는 로우만 보여지기 0 3 539

by 재래개 [SQL Query] [2018.07.19 11:42:38]


안녕하세요.

푹푹찌는 무더위입니다 ㅠㅠ 

제 머리도 푹푹찌네요..

 

원하는(특정) 수량만큼 대상 테이블의 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

 

부탁드립니다!

 

by 신이만든지기 [2018.07.19 11:58:55]
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 
    

 


by 우리집아찌 [2018.07.19 12:28:57]
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 )

 


by 마농 [2018.07.23 10:00:45]

합계가 딱 맞아 떨어지지 않는 경우를 감안해야 할 듯 합니다.

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
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입