데이터베이스 질문과 답변
납품일자별 MIN 값 데이터 가져오기 질문 드립니다! (FOR LOOP x) 마농님 부탁드립니다!
0
2
1,636
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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | 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]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 | WITH w_name AS(SELECT 'A' item_code, TO_DATE('20190323', 'yyyymmdd') 납품일 FROM dualUNION ALL SELECT 'B', TO_DATE('20190329', 'yyyymmdd') FROM dualUNION ALL SELECT 'C', TO_DATE('20190325', 'yyyymmdd') FROM dualUNION ALL SELECT 'D', TO_DATE('20190326', 'yyyymmdd') FROM dualUNION ALL SELECT 'E', TO_DATE('20190324', 'yyyymmdd') FROM dualUNION ALL SELECT 'F', TO_DATE('20190325', 'yyyymmdd') FROM dualUNION ALL SELECT 'G', TO_DATE('20190328', 'yyyymmdd') FROM dual), w_time AS(SELECT 'A' from_item, 'B' to_item, 5 아이템변경시간 FROM dualUNION ALL SELECT 'A', 'F', 5 FROM dualUNION ALL SELECT 'A', 'C', 10 FROM dualUNION ALL SELECT 'A', 'D', 10 FROM dualUNION ALL SELECT 'A', 'E', 10 FROM dualUNION ALL SELECT 'A', 'G', 5 FROM dualUNION ALL SELECT 'B', 'A', 10 FROM dualUNION ALL SELECT 'B', 'F', 5 FROM dualUNION ALL SELECT 'B', 'C', 5 FROM dualUNION ALL SELECT 'B', 'D', 10 FROM dualUNION ALL SELECT 'B', 'E', 5 FROM dualUNION ALL SELECT 'B', 'G', 5 FROM dualUNION ALL SELECT 'F', 'A', 5 FROM dualUNION ALL SELECT 'F', 'B', 5 FROM dualUNION ALL SELECT 'F', 'C', 5 FROM dualUNION ALL SELECT 'F', 'D', 10 FROM dualUNION ALL SELECT 'F', 'E', 20 FROM dualUNION ALL SELECT 'F', 'G', 10 FROM dualUNION ALL SELECT 'E', 'A', 10 FROM dualUNION ALL SELECT 'E', 'B', 10 FROM dualUNION ALL SELECT 'E', 'C', 5 FROM dualUNION ALL SELECT 'E', 'D', 10 FROM dualUNION ALL SELECT 'E', 'F', 10 FROM dualUNION ALL SELECT 'E', 'G', 5 FROM dualUNION ALL SELECT 'C', 'A', 5 FROM dualUNION ALL SELECT 'C', 'B', 5 FROM dualUNION ALL SELECT 'C', 'E', 5 FROM dualUNION ALL SELECT 'C', 'D', 5 FROM dualUNION ALL SELECT 'C', 'F', 10 FROM dualUNION ALL SELECT 'C', 'G', 10 FROM dualUNION ALL SELECT 'G', 'A', 5 FROM dualUNION ALL SELECT 'G', 'B', 10 FROM dualUNION ALL SELECT 'G', 'E', 10 FROM dualUNION ALL SELECT 'G', 'D', 5 FROM dualUNION ALL SELECT 'G', 'F', 5 FROM dualUNION ALL SELECT 'G', 'C', 10 FROM dualUNION ALL SELECT 'D', 'A', 10 FROM dualUNION ALL SELECT 'D', 'B', 5 FROM dualUNION ALL SELECT 'D', 'E', 5 FROM dual--UNION ALL SELECT 'D', 'D', 10 FROM dualUNION ALL SELECT 'D', 'G', 10 FROM dual -- 오타 수정UNION ALL SELECT 'D', 'F', 10 FROM dualUNION 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 ALLSELECT 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; |
) 버튼을 클릭하여 작성 하시면 됩니다.