꿈꾸는 개발자, DBA 커뮤니티 구루비

납품일자별 MIN 값 데이터 가져오기 질문 드립니다! (FOR LOOP x) 마농님 부탁드립니다!

by 홍길덩이 [SQL Query] 오라클 [2019.03.25 11:04:25]

안녕하세요!

아래 내용중 작업일정 결과 화면처럼 출력하고 싶은데

FOR LOOP, WILE 문등 사용하지 않고

쿼리 하나로 끝내고 싶습니다...

해보려도 해도 잘 안나오는데

ITEM FROM -> TO 방식으로

정렬 순서는 [아이템 변경시간] 빠른순 - MIN값 > 납품일 빠른순 데이터를 JOIN해서 가져오고 싶습니다.

도움 부탁드립니다!

--아이템 변경시간

FROM_ITEM	TO_ITEM	아이템변경시간
A	B	5
A	F	5
A	C	10
A	D	10
A	E	10
A	G	5
B	A	10
B	F	5
B	C	5
B	D	10
B	E	5
B	G	5
F	A	5
F	B	5
F	C	5
F	D	10
F	E	20
F	G	10
E	A	10
E	B	10
E	C	5
E	D	10
E	F	10
E	G	5
C	A	5
C	B	5
C	E	5
C	D	5
C	F	10
C	G	10
G	A	5
G	B	10
G	E	10
G	D	5
G	F	5
G	C	10
D	A	10
D	B	5
D	E	5
D	D	10
D	F	10
D	C	10

-- ITME 정보

ITEM_CODE	납품일
A	2019-03-23 0:00
B	2019-03-29 0:00
C	2019-03-25 0:00
D	2019-03-26 0:00
E	2019-03-24 0:00
F	2019-03-25 0:00
G	2019-03-28 0:00

-- 작업일정 결과화면

ITEM_CODE	납품일	변경아이템	변경시간
A	2019-03-23 0:00	F	5
F	2019-03-25 0:00	C	5
C	2019-03-25 0:00	E	5
E	2019-03-24 0:00	G	5
G	2019-03-28 0:00	D	5
D	2019-03-26 0:00	B	5
B	2019-03-29 0:00

WITH W_NAME AS
    (
    SELECT  'A' AS ITEM_CODE, TO_DATE('20190323','YYYYMMDD') AS "납품일" FROM DUAL UNION ALL
    SELECT  'B' AS ITEM_CODE, TO_DATE('20190329','YYYYMMDD') AS "납품일" FROM DUAL UNION ALL
    SELECT  'C' AS ITEM_CODE, TO_DATE('20190325','YYYYMMDD') AS "납품일" FROM DUAL UNION ALL
    SELECT  'D' AS ITEM_CODE, TO_DATE('20190326','YYYYMMDD') AS "납품일" FROM DUAL UNION ALL
    SELECT  'E' AS ITEM_CODE, TO_DATE('20190324','YYYYMMDD') AS "납품일" FROM DUAL UNION ALL
    SELECT  'F' AS ITEM_CODE, TO_DATE('20190325','YYYYMMDD') AS "납품일" FROM DUAL UNION ALL
    SELECT  'G' AS ITEM_CODE, TO_DATE('20190328','YYYYMMDD') AS "납품일" FROM DUAL
    )
    ,   W_TIME AS 
    (
    SELECT  'A' AS FROM_ITEM, 'B' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'A' AS FROM_ITEM, 'F' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'A' AS FROM_ITEM, 'C' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'A' AS FROM_ITEM, 'D' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'A' AS FROM_ITEM, 'E' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'A' AS FROM_ITEM, 'G' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'B' AS FROM_ITEM, 'A' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'B' AS FROM_ITEM, 'F' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'B' AS FROM_ITEM, 'C' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'B' AS FROM_ITEM, 'D' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'B' AS FROM_ITEM, 'E' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'B' AS FROM_ITEM, 'G' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'F' AS FROM_ITEM, 'A' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'F' AS FROM_ITEM, 'B' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'F' AS FROM_ITEM, 'C' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'F' AS FROM_ITEM, 'D' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'F' AS FROM_ITEM, 'E' AS TO_ITEM, 20 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'F' AS FROM_ITEM, 'G' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'E' AS FROM_ITEM, 'A' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'E' AS FROM_ITEM, 'B' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'E' AS FROM_ITEM, 'C' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'E' AS FROM_ITEM, 'D' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'E' AS FROM_ITEM, 'F' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'E' AS FROM_ITEM, 'G' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'C' AS FROM_ITEM, 'A' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'C' AS FROM_ITEM, 'B' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'C' AS FROM_ITEM, 'E' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'C' AS FROM_ITEM, 'D' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'C' AS FROM_ITEM, 'F' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'C' AS FROM_ITEM, 'G' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'G' AS FROM_ITEM, 'A' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'G' AS FROM_ITEM, 'B' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'G' AS FROM_ITEM, 'E' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'G' AS FROM_ITEM, 'D' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'G' AS FROM_ITEM, 'F' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'G' AS FROM_ITEM, 'C' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'D' AS FROM_ITEM, 'A' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'D' AS FROM_ITEM, 'B' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'D' AS FROM_ITEM, 'E' AS TO_ITEM, 5 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'D' AS FROM_ITEM, 'D' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'D' AS FROM_ITEM, 'F' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL UNION ALL
    SELECT  'D' AS FROM_ITEM, 'C' AS TO_ITEM, 10 AS "아이템변경시간" FROM DUAL 
    )

by 홍길덩이 [2019.03.25 15:39:05]

아이템을 생산할때 아이템변경시간이 빠른순서대로 생산하는데, 아이템변경시간과 납품일을 기준으로 데이터를 정렬하고 싶습니다! 도움 부탁드려요~

생각보다 잘안되서 질문드려요~

쿼리 하나로 안된다면 다른방식으로도 부탁드립니다^^

by 마농 [2019.03.25 16:31:00]

WITH w_name AS
(
SELECT 'A' item_code, TO_DATE('20190323', 'yyyymmdd') 납품일 FROM dual
UNION ALL SELECT 'B', TO_DATE('20190329', 'yyyymmdd') FROM dual
UNION ALL SELECT 'C', TO_DATE('20190325', 'yyyymmdd') FROM dual
UNION ALL SELECT 'D', TO_DATE('20190326', 'yyyymmdd') FROM dual
UNION ALL SELECT 'E', TO_DATE('20190324', 'yyyymmdd') FROM dual
UNION ALL SELECT 'F', TO_DATE('20190325', 'yyyymmdd') FROM dual
UNION ALL SELECT 'G', TO_DATE('20190328', 'yyyymmdd') FROM dual
)
, w_time AS
(
SELECT 'A' from_item, 'B' to_item, 5 아이템변경시간 FROM dual
UNION ALL SELECT 'A', 'F',  5 FROM dual
UNION ALL SELECT 'A', 'C', 10 FROM dual
UNION ALL SELECT 'A', 'D', 10 FROM dual
UNION ALL SELECT 'A', 'E', 10 FROM dual
UNION ALL SELECT 'A', 'G',  5 FROM dual
UNION ALL SELECT 'B', 'A', 10 FROM dual
UNION ALL SELECT 'B', 'F',  5 FROM dual
UNION ALL SELECT 'B', 'C',  5 FROM dual
UNION ALL SELECT 'B', 'D', 10 FROM dual
UNION ALL SELECT 'B', 'E',  5 FROM dual
UNION ALL SELECT 'B', 'G',  5 FROM dual
UNION ALL SELECT 'F', 'A',  5 FROM dual
UNION ALL SELECT 'F', 'B',  5 FROM dual
UNION ALL SELECT 'F', 'C',  5 FROM dual
UNION ALL SELECT 'F', 'D', 10 FROM dual
UNION ALL SELECT 'F', 'E', 20 FROM dual
UNION ALL SELECT 'F', 'G', 10 FROM dual
UNION ALL SELECT 'E', 'A', 10 FROM dual
UNION ALL SELECT 'E', 'B', 10 FROM dual
UNION ALL SELECT 'E', 'C',  5 FROM dual
UNION ALL SELECT 'E', 'D', 10 FROM dual
UNION ALL SELECT 'E', 'F', 10 FROM dual
UNION ALL SELECT 'E', 'G',  5 FROM dual
UNION ALL SELECT 'C', 'A',  5 FROM dual
UNION ALL SELECT 'C', 'B',  5 FROM dual
UNION ALL SELECT 'C', 'E',  5 FROM dual
UNION ALL SELECT 'C', 'D',  5 FROM dual
UNION ALL SELECT 'C', 'F', 10 FROM dual
UNION ALL SELECT 'C', 'G', 10 FROM dual
UNION ALL SELECT 'G', 'A',  5 FROM dual
UNION ALL SELECT 'G', 'B', 10 FROM dual
UNION ALL SELECT 'G', 'E', 10 FROM dual
UNION ALL SELECT 'G', 'D',  5 FROM dual
UNION ALL SELECT 'G', 'F',  5 FROM dual
UNION ALL SELECT 'G', 'C', 10 FROM dual
UNION ALL SELECT 'D', 'A', 10 FROM dual
UNION ALL SELECT 'D', 'B',  5 FROM dual
UNION ALL SELECT 'D', 'E',  5 FROM dual
--UNION ALL SELECT 'D', 'D', 10 FROM dual
UNION ALL SELECT 'D', 'G', 10 FROM dual  -- 오타 수정
UNION ALL SELECT 'D', 'F', 10 FROM dual
UNION ALL SELECT 'D', 'C', 10 FROM dual
)
, w_temp(item_path, lv, item_code, 납품일, to_item) AS
(
SELECT CAST('-' || item_code AS VARCHAR2(99)) item_path
     , 1 lv
     , item_code
     , 납품일
     , (SELECT MIN(b.to_item) KEEP(DENSE_RANK FIRST ORDER BY b.아이템변경시간, c.납품일, b.to_item) to_item
          FROM w_time b
             , w_name c
         WHERE b.from_item = a.item_code
           AND c.item_code = b.to_item
        ) to_item
  FROM w_name a
 WHERE item_code = (SELECT MIN(item_code) KEEP(DENSE_RANK FIRST ORDER BY 납품일, item_code) FROM w_name)
 UNION ALL
SELECT d.item_path || '-' || a.item_code item_path
     , d.lv + 1 lv
     , a.item_code
     , a.납품일
     , (SELECT MIN(b.to_item) KEEP(DENSE_RANK FIRST ORDER BY b.아이템변경시간, c.납품일, b.to_item) to_item
          FROM w_time b
             , w_name c
         WHERE b.from_item = a.item_code
           AND c.item_code = b.to_item
           AND INSTR(d.item_path||'-', '-'||b.to_item||'-') = 0
        ) to_item
  FROM w_name a
     , w_temp d
 WHERE a.item_code = d.to_item
)
SELECT a.item_code
     , a.납품일
     , a.to_item
     , b.아이템변경시간
  FROM w_temp a
     , w_time b
 WHERE a.item_code = b.from_item(+)
   AND a.to_item   = b.to_item(+)
 ORDER BY a.lv
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변