데이터베이스 질문과 답변

납품일자별 MIN 값 데이터 가져오기 (FOR LOOP x) - 이전 질문 조금다른내용 0 11 1,431

by 홍길덩이 [PL/SQL] 오라클 [2019.04.16 11:17:46]

안녕하세요~

저번질문에서 마농님께서 답변을 올려 주셨었는데..

데이터가 이상한것 같다고 하셔서 다시 찾아보니 업체코드와 작업주 등 여러가지 요소가 빠져있었습니다.

데이터 추가하여 문의 드립니다. 

 

W_NAME 과 W_TIME을 JOIN하여 정렬순서를 정하게 되는데

정렬 순서는 S_TIME 빠른순 > 아이템그룹 > 납품일 빠른순 > 아이템타입( 0 우선적용, 1,2,3,4....같은순위) > 용량 큰순

으로 정렬 되어야하며, 동일 정렬순서의 다른 라인일 경우 동일한 순번을 같습니다.

예를 들어 A (1) -> A (2) -> A (3) -> B (3) -> A (4) -> A(5) -> B(5) -> A(6) ...

이런 식으로 순번이 정해집니다.

그리고 작업주의 마지막 아이템과 그 다음 작업주의 아이템중 가장 빠른 정렬순서를 같는 아이템이 먼저 순서를 부여받게 됩니다.

잘 부탁드립니다(--)(__)

 

업체코드 아이템 아이템타입 작업주 아이템그룹 용량 납품일 작업구역
AP001-1 AT_20 1 1 AT 10 2019-04-01 0:00 A
AP001-2 AT_20 2 1 AT 20 2019-04-01 0:00 A
AP001-0 AT_20 0 2 AT 5 2019-04-02 0:00 A
AP003-1 AT_21 1 2 AT 18 2019-04-03 0:00 A
AP004-1 AT_22 1 2 AT 54 2019-04-02 0:00 C
AP005-1 AT_23 1 2 AT 8 2019-04-02 0:00 C
AP006-1 BA_20 1 2 BA 21 2019-04-02 0:00 A
AP007-1 BA_22 1 2 BA 41 2019-04-02 0:00 A
AP007-2 AT_22 2 2 AT 13 2019-04-04 0:00 A
AP007-3 AT_24 3 3 AT 1 2019-04-04 0:00 A
AP007-4 AT_25 4 3 AT 2 2019-04-04 0:00 A
AP001-1 AT_25 1 3 AT 3 2019-04-04 0:00 A
AP001-2 AT_26 2 3 AT 4 2019-04-04 0:00 A
AP002-1 AT_26 1 4 AT 7 2019-04-05 0:00 A
AP003-1 BA_27 1 4 BA 22 2019-04-05 0:00 A
AP004-1 AT_27 1 5 AT 26 2019-04-06 0:00 A
AP005-0 AT_28 0 5 AT 1 2019-04-07 0:00 A

 

--결과

업체코드 아이템 아이템타입 작업주 아이템그룹 용량 납품일 작업구역 순번
AP001-2 AT_20 2 1 AT 20 2019-04-01 0:00 A 1
AP001-1 AT_20 1 1 AT 10 2019-04-01 0:00 A 2
AP001-0 AT_20 0 2 AT 5 2019-04-02 0:00 A 3
AP004-1 AT_22 1 2 AT 54 2019-04-02 0:00 C 3
AP003-1 AT_21 1 2 AT 18 2019-04-03 0:00 A 4
AP005-1 AT_23 1 2 AT 8 2019-04-02 0:00 C 4
AP007-2 AT_22 2 2 AT 13 2019-04-04 0:00 A 5
AP007-1 BA_22 1 2 BA 41 2019-04-02 0:00 A 6
AP006-1 BA_20 1 2 BA 21 2019-04-02 0:00 A 7
AP001-2 AT_26 2 3 AT 4 2019-04-04 0:00 A 8
AP001-1 AT_25 1 3 AT 3 2019-04-04 0:00 A 9
AP007-3 AT_24 3 3 AT 1 2019-04-04 0:00 A 10
AP007-4 AT_25 4 3 AT 2 2019-04-04 0:00 A 11
AP002-1 AT_26 1 4 AT 7 2019-04-05 0:00 A 12
AP003-1 BA_27 1 4 BA 22 2019-04-05 0:00 A 13
AP004-1 AT_27 1 5 AT 26 2019-04-06 0:00 A 14
AP005-0 AT_28 0 5 AT 1 2019-04-07 0:00 A 15

--W_TIME

FROM_ITEM TO_ITME S_TIME
AT_20 AT_20 0
AT_20 AT_21 5
AT_20 AT_22 5
AT_20 AT_23 5
AT_20 AT_24 5
AT_20 AT_25 5
AT_20 AT_26 5
AT_20 AT_27 5
AT_20 AT_28 5
AT_20 BA_20 10
AT_20 BA_22 10
AT_20 BA_25 10
AT_20 BA_26 10
AT_20 BA_27 10
AT_21 AT_20 5
AT_21 AT_21 0
AT_21 AT_22 5
AT_21 AT_23 5
AT_21 AT_24 5
AT_21 AT_25 5
AT_21 AT_26 5
AT_21 AT_27 5
AT_21 AT_28 5
AT_21 BA_20 10
AT_21 BA_22 10
AT_21 BA_25 10
AT_21 BA_26 10
AT_21 BA_27 10
AT_22 AT_20 5
AT_22 AT_21 5
AT_22 AT_22 0
AT_22 AT_23 5
AT_22 AT_24 5
AT_22 AT_25 5
AT_22 AT_26 5
AT_22 AT_27 5
AT_22 AT_28 5
AT_22 BA_20 10
AT_22 BA_22 10
AT_22 BA_25 10
AT_22 BA_26 10
AT_22 BA_27 10
AT_23 AT_20 5
AT_23 AT_21 5
AT_23 AT_22 5
AT_23 AT_23 0
AT_23 AT_24 5
AT_23 AT_25 5
AT_23 AT_26 5
AT_23 AT_27 5
AT_23 AT_28 5
AT_23 BA_20 10
AT_23 BA_22 10
AT_23 BA_25 10
AT_23 BA_26 10
AT_23 BA_27 10
AT_24 AT_20 5
AT_24 AT_21 5
AT_24 AT_22 5
AT_24 AT_23 5
AT_24 AT_24 0
AT_24 AT_25 5
AT_24 AT_26 5
AT_24 AT_27 5
AT_24 AT_28 5
AT_24 BA_20 10
AT_24 BA_22 10
AT_24 BA_25 10
AT_24 BA_26 10
AT_24 BA_27 10
AT_25 AT_20 5
AT_25 AT_21 5
AT_25 AT_22 5
AT_25 AT_23 5
AT_25 AT_24 5
AT_25 AT_25 0
AT_25 AT_26 5
AT_25 AT_27 5
AT_25 AT_28 5
AT_25 BA_20 10
AT_25 BA_22 10
AT_25 BA_25 10
AT_25 BA_26 10
AT_25 BA_27 10
AT_26 AT_20 5
AT_26 AT_21 5
AT_26 AT_22 5
AT_26 AT_23 5
AT_26 AT_24 5
AT_26 AT_25 5
AT_26 AT_26 0
AT_26 AT_27 5
AT_26 AT_28 5
AT_26 BA_20 10
AT_26 BA_22 10
AT_26 BA_25 10
AT_26 BA_26 10
AT_26 BA_27 10
AT_27 AT_20 5
AT_27 AT_21 5
AT_27 AT_22 5
AT_27 AT_23 5
AT_27 AT_24 5
AT_27 AT_25 5
AT_27 AT_26 5
AT_27 AT_27 0
AT_27 AT_28 5
AT_27 BA_20 10
AT_27 BA_22 10
AT_27 BA_25 10
AT_27 BA_26 10
AT_27 BA_27 10
AT_28 AT_20 5
AT_28 AT_21 5
AT_28 AT_22 5
AT_28 AT_23 5
AT_28 AT_24 5
AT_28 AT_25 5
AT_28 AT_26 5
AT_28 AT_27 5
AT_28 AT_28 0
AT_28 BA_20 10
AT_28 BA_22 10
AT_28 BA_25 10
AT_28 BA_26 10
AT_28 BA_27 10
BA_20 AT_20 10
BA_20 AT_21 10
BA_20 AT_22 10
BA_20 AT_23 10
BA_20 AT_24 10
BA_20 AT_25 10
BA_20 AT_26 10
BA_20 AT_27 10
BA_20 AT_28 10
BA_20 BA_20 0
BA_20 BA_22 5
BA_20 BA_25 5
BA_20 BA_26 5
BA_20 BA_27 5
BA_22 AT_20 10
BA_22 AT_21 10
BA_22 AT_22 10
BA_22 AT_23 10
BA_22 AT_24 10
BA_22 AT_25 10
BA_22 AT_26 10
BA_22 AT_27 10
BA_22 AT_28 10
BA_22 BA_20 5
BA_22 BA_22 0
BA_22 BA_25 5
BA_22 BA_26 5
BA_22 BA_27 5
BA_25 AT_20 10
BA_25 AT_21 10
BA_25 AT_22 10
BA_25 AT_23 10
BA_25 AT_24 10
BA_25 AT_25 10
BA_25 AT_26 10
BA_25 AT_27 10
BA_25 AT_28 10
BA_25 BA_20 5
BA_25 BA_22 5
BA_25 BA_25 0
BA_25 BA_26 5
BA_25 BA_27 5
BA_26 AT_20 10
BA_26 AT_21 10
BA_26 AT_22 10
BA_26 AT_23 10
BA_26 AT_24 10
BA_26 AT_25 10
BA_26 AT_26 10
BA_26 AT_27 10
BA_26 AT_28 10
BA_26 BA_20 5
BA_26 BA_22 5
BA_26 BA_25 5
BA_26 BA_26 0
BA_26 BA_27 5
BA_27 AT_20 10
BA_27 AT_21 10
BA_27 AT_22 10
BA_27 AT_23 10
BA_27 AT_24 10
BA_27 AT_25 10
BA_27 AT_26 10
BA_27 AT_27 10
BA_27 AT_28 10
BA_27 BA_20 5
BA_27 BA_22 5
BA_27 BA_25 5
BA_27 BA_26 5
BA_27 BA_27 0

 

?
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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
WITH W_NAME AS
    (
    SELECT 'AP001-1' AS "업체코드", 'AT_20' AS "아이템", '1' AS "아이템타입", '1' AS "작업주", 'AT' AS "아이템그룹", 10 "용량", TO_DATE('20190401','YYYYMMDD') AS "납품일", 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP001-2', 'AT_20' AS "아이템", '2' AS "아이템타입", '1' AS "작업주", 'AT' AS "아이템그룹", 20 "용량", TO_DATE('20190401','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP001-0', 'AT_20' AS "아이템", '0' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 5 "용량", TO_DATE('20190402','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP003-1', 'AT_21' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 18 "용량", TO_DATE('20190403','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP004-1', 'AT_22' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 54 "용량", TO_DATE('20190402','YYYYMMDD'), 'C' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP005-1', 'AT_23' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 8 "용량", TO_DATE('20190402','YYYYMMDD'), 'C' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP006-1', 'BA_20' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'BA' AS "아이템그룹", 21 "용량", TO_DATE('20190402','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP007-1', 'BA_22' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'BA' AS "아이템그룹", 41 "용량", TO_DATE('20190402','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP007-2', 'AT_22' AS "아이템", '2' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 13 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP007-3', 'AT_24' AS "아이템", '3' AS "아이템타입", '3' AS "작업주", 'AT' AS "아이템그룹", 1 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP007-4', 'AT_25' AS "아이템", '4' AS "아이템타입", '3' AS "작업주", 'AT' AS "아이템그룹", 2 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP001-1', 'AT_25' AS "아이템", '1' AS "아이템타입", '3' AS "작업주", 'AT' AS "아이템그룹", 3 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP001-2', 'AT_26' AS "아이템", '2' AS "아이템타입", '3' AS "작업주", 'AT' AS "아이템그룹", 4 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP002-1', 'AT_26' AS "아이템", '1' AS "아이템타입", '4' AS "작업주", 'AT' AS "아이템그룹", 7 "용량", TO_DATE('20190405','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP003-1', 'BA_27' AS "아이템", '1' AS "아이템타입", '4' AS "작업주", 'BA' AS "아이템그룹", 22 "용량", TO_DATE('20190405','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP004-1', 'AT_27' AS "아이템", '1' AS "아이템타입", '5' AS "작업주", 'AT' AS "아이템그룹", 26 "용량", TO_DATE('20190406','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP005-0', 'AT_28' AS "아이템", '0' AS "아이템타입", '5' AS "작업주", 'AT' AS "아이템그룹", 1 "용량", TO_DATE('20190407','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    )
,   W_TIME AS
    (
    SELECT 'AT_20' FROM_ITEM,   'AT_20' TO_ITEM, 0 AS S_TIME  FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'AT_21' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'AT_22' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'AT_23' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'AT_24' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'AT_25' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'AT_26' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'AT_27' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'AT_28' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'BA_20' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'BA_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'BA_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'BA_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'BA_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'BA_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'BA_22' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'BA_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'BA_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'BA_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'BA_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'BA_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'BA_25' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'BA_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'BA_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'BA_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'BA_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'BA_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'BA_26' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'BA_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'BA_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'BA_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'BA_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'BA_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'BA_27' TO_ITEM, 0    FROM DUAL
    )

 

by 마농 [2019.04.16 13:20:49]

두번째 표가 원하는 결과인가요?                             
이 결과에 대한 설명을 해주세요.                            
왜 이런 결과가 나오게 되는지 과정을 차근 차근 설명해 주세요.

by 홍길덩이 [2019.04.16 13:40:24]

작업주와 정렬순서대로 작성하여 나온 결과입니다.

어떤 부분에서 이상한지 알려주시면 말씀드리겠습니다.

 

-- S_TIME 적용 후 (순번추가)

업체코드 아이템 아이템타입 작업주 아이템그룹 용량 납품일 작업구역 순번
AP001-2 AT_20 2 1 AT 20 2019-04-01 0:00 A 1
AP001-1 AT_20 1 1 AT 10 2019-04-01 0:00 A 2
AP001-0 AT_20 0 2 AT 5 2019-04-02 0:00 A 3
AP004-1 AT_22 1 2 AT 54 2019-04-02 0:00 C 3
AP005-1 AT_23 1 2 AT 8 2019-04-02 0:00 C 4
AP003-1 AT_21 1 2 AT 18 2019-04-03 0:00 A 4
AP007-2 AT_22 2 2 AT 13 2019-04-04 0:00 A 5
AP007-1 BA_22 1 2 BA 41 2019-04-02 0:00 A 6
AP006-1 BA_20 1 2 BA 21 2019-04-02 0:00 A 7
AP001-2 AT_26 2 3 AT 4 2019-04-04 0:00 A 8
AP001-1 AT_25 1 3 AT 3 2019-04-04 0:00 A 9
AP007-3 AT_24 3 3 AT 1 2019-04-04 0:00 A 10
AP007-4 AT_25 4 3 AT 2 2019-04-04 0:00 A 11
AP002-1 AT_26 1 4 AT 7 2019-04-05 0:00 A 12
AP003-1 BA_27 1 4 BA 22 2019-04-05 0:00 A 13
AP004-1 AT_27 1 5 AT 26 2019-04-06 0:00 A 14
AP005-0 AT_28 0 5 AT 1 2019-04-07 0:00 A 14

맨마지막 작업구역에 B로 되어있는데 A로 보시면 됩니다.

 

by 마농 [2019.04.16 13:43:08]

한줄 한줄 따라가면서 자세히 설명해 주세요.

by 홍길덩이 [2019.04.16 14:11:28]

아이템 생산하는 과정에서 가장 최적의 결과를 얻기위해 

S_TIME 빠른순 > 아이템그룹 > 납품일 빠른순 > 아이템타입( 0 우선적용, 1,2,3,4....같은순위) > 용량 큰순

정렬 순서대로 생산을 하게 됩니다.

 

작업주는 1주차 2주차 3주차... 순

작업구역 A , B, C, D.. 순

순번1번의 경우

  - 업체코드 AP001-2 , 아이템 AT_20, 용량 20, 납품일 2019-04-01 0:00  되며,

순번2번의 경우

    같은 작업주이면서 순번1번과 연결되는 아이템이 생산되어야 합니다.

업체코드 AP001-1 아이템 AT_20, 옹량 10, 납품일 2019-04-01 0:00,  AT_20 ->AT_20 연결의 경우 S_TIME = 0 이므로  정렬순서의 따라 순번2번이 됩니다.

순번3번의 A 작업구역의 경우

    이전 1 작업주의 마지막 아이템과 연결하여 생산하기 위해 2 작업주의 아이템중 정렬순서의 따라

업체코드 AP001-0, 아이템 AT_20, 아이템타입 0, 용량 5, 납품일 2019-04-02 0:00 로 다른 아이템의 비해 용량은 작지만

AT_20 -> AT_20의 S_TIME 은 0 이면서 우선적용인 아이템 타입이 0이며 납품일자가 가장 빠르기 때문에 순번 3번이 됩니다.

순번 3번에는 A,C 작업 구역이 같이 있는데 같은 주자에 여러개의 작업구역이 있는경우 같은 순번을 가지게 됩니다.

C 작업구역의 순번 4번은 순번3번의 C 작업구역과 S_TIME 이 5 이면서 납품일이 가장 빠르기 때문에 순번 4번을 가지게 됩니다.

 

 

by 홍길덩이 [2019.04.16 14:11:55]

더 셜명이 필요한가요? ^^;;

by 홍길덩이 [2019.04.16 14:21:56]

순번4번의 A작업 구역은

순번3번의 A작업 구역의 아이템과

S_TIME 빠른순 > 아이템그룹 > 납품일 빠른순 > 아이템타입( 0 우선적용, 1,2,3,4....같은순위) > 용량 큰순

으로 데이터를 보면 2주차의 AT_20 과 S_TIME가장 적은 아이템은 AT_21, AT_22 이며

그 중 납품일이 가장 빠르기 때문에

업체코드 AP003-1, 아이템 AT_21, 18 용량 2019-04-03 0:00 이 됩니다.

 

순번 5번의 A작업구역은

순번 4번의 A자업구역의 아이템과 S_TIME이 가장 적은 업체코드 AP_007-2, 아이템 AT_22, 13 용량, 2019-04-04 0:00 납품일 이 됩니다.

by 마농 [2019.04.16 14:54:49]

자료가 왔다 갔다 서로 불일치 하네요.
원본표, 결과표, WITH 문, 댓글표 서로 불일치.
다 지우고 정확하게 일치하는 자료로 새로 올려주세요.

by 홍길덩이 [2019.04.16 15:10:59]

죄송합니다. 다시올립니다.

--원본

업체코드 아이템 아이템타입 작업주 아이템그룹 용량 납품일 작업구역
AP001-1 AT_20 1 1 AT 10 2019-04-01 0:00 A
AP001-2 AT_20 2 1 AT 20 2019-04-01 0:00 A
AP001-0 AT_20 0 2 AT 5 2019-04-02 0:00 A
AP003-1 AT_21 1 2 AT 18 2019-04-03 0:00 A
AP004-1 AT_22 1 2 AT 54 2019-04-02 0:00 C
AP005-1 AT_23 1 2 AT 8 2019-04-02 0:00 C
AP006-1 BA_20 1 2 BA 21 2019-04-02 0:00 A
AP007-1 BA_22 1 2 BA 41 2019-04-02 0:00 A
AP007-2 AT_22 2 2 AT 13 2019-04-04 0:00 A
AP007-3 AT_24 3 3 AT 1 2019-04-04 0:00 A
AP007-4 AT_25 4 3 AT 2 2019-04-04 0:00 A
AP001-1 AT_25 1 3 AT 3 2019-04-04 0:00 A
AP001-2 AT_26 2 3 AT 4 2019-04-04 0:00 A
AP002-1 AT_26 1 4 AT 7 2019-04-05 0:00 A
AP003-1 BA_27 1 4 BA 22 2019-04-05 0:00 A
AP004-1 AT_27 1 5 AT 26 2019-04-06 0:00 A
AP005-0 AT_28 0 5 AT 1 2019-04-07 0:00 A

 

--결과

업체코드 아이템 아이템타입 작업주 아이템그룹 용량 납품일 작업구역 순번
AP001-2 AT_20 2 1 AT 20 2019-04-01 0:00 A 1
AP001-1 AT_20 1 1 AT 10 2019-04-01 0:00 A 2
AP001-0 AT_20 0 2 AT 5 2019-04-02 0:00 A 3
AP004-1 AT_22 1 2 AT 54 2019-04-02 0:00 C 3
AP003-1 AT_21 1 2 AT 18 2019-04-03 0:00 A 4
AP005-1 AT_23 1 2 AT 8 2019-04-02 0:00 C 4
AP007-2 AT_22 2 2 AT 13 2019-04-04 0:00 A 5
AP007-1 BA_22 1 2 BA 41 2019-04-02 0:00 A 6
AP006-1 BA_20 1 2 BA 21 2019-04-02 0:00 A 7
AP001-2 AT_26 2 3 AT 4 2019-04-04 0:00 A 8
AP001-1 AT_25 1 3 AT 3 2019-04-04 0:00 A 9
AP007-3 AT_24 3 3 AT 1 2019-04-04 0:00 A 10
AP007-4 AT_25 4 3 AT 2 2019-04-04 0:00 A 11
AP002-1 AT_26 1 4 AT 7 2019-04-05 0:00 A 12
AP003-1 BA_27 1 4 BA 22 2019-04-05 0:00 A 13
AP004-1 AT_27 1 5 AT 26 2019-04-06 0:00 A 14
AP005-0 AT_28 0 5 AT 1 2019-04-07 0:00 A 15

--W_TIME

FROM_ITEM TO_ITME S_TIME
AT_20 AT_20 0
AT_20 AT_21 5
AT_20 AT_22 5
AT_20 AT_23 5
AT_20 AT_24 5
AT_20 AT_25 5
AT_20 AT_26 5
AT_20 AT_27 5
AT_20 AT_28 5
AT_20 BA_20 10
AT_20 BA_22 10
AT_20 BA_25 10
AT_20 BA_26 10
AT_20 BA_27 10
AT_21 AT_20 5
AT_21 AT_21 0
AT_21 AT_22 5
AT_21 AT_23 5
AT_21 AT_24 5
AT_21 AT_25 5
AT_21 AT_26 5
AT_21 AT_27 5
AT_21 AT_28 5
AT_21 BA_20 10
AT_21 BA_22 10
AT_21 BA_25 10
AT_21 BA_26 10
AT_21 BA_27 10
AT_22 AT_20 5
AT_22 AT_21 5
AT_22 AT_22 0
AT_22 AT_23 5
AT_22 AT_24 5
AT_22 AT_25 5
AT_22 AT_26 5
AT_22 AT_27 5
AT_22 AT_28 5
AT_22 BA_20 10
AT_22 BA_22 10
AT_22 BA_25 10
AT_22 BA_26 10
AT_22 BA_27 10
AT_23 AT_20 5
AT_23 AT_21 5
AT_23 AT_22 5
AT_23 AT_23 0
AT_23 AT_24 5
AT_23 AT_25 5
AT_23 AT_26 5
AT_23 AT_27 5
AT_23 AT_28 5
AT_23 BA_20 10
AT_23 BA_22 10
AT_23 BA_25 10
AT_23 BA_26 10
AT_23 BA_27 10
AT_24 AT_20 5
AT_24 AT_21 5
AT_24 AT_22 5
AT_24 AT_23 5
AT_24 AT_24 0
AT_24 AT_25 5
AT_24 AT_26 5
AT_24 AT_27 5
AT_24 AT_28 5
AT_24 BA_20 10
AT_24 BA_22 10
AT_24 BA_25 10
AT_24 BA_26 10
AT_24 BA_27 10
AT_25 AT_20 5
AT_25 AT_21 5
AT_25 AT_22 5
AT_25 AT_23 5
AT_25 AT_24 5
AT_25 AT_25 0
AT_25 AT_26 5
AT_25 AT_27 5
AT_25 AT_28 5
AT_25 BA_20 10
AT_25 BA_22 10
AT_25 BA_25 10
AT_25 BA_26 10
AT_25 BA_27 10
AT_26 AT_20 5
AT_26 AT_21 5
AT_26 AT_22 5
AT_26 AT_23 5
AT_26 AT_24 5
AT_26 AT_25 5
AT_26 AT_26 0
AT_26 AT_27 5
AT_26 AT_28 5
AT_26 BA_20 10
AT_26 BA_22 10
AT_26 BA_25 10
AT_26 BA_26 10
AT_26 BA_27 10
AT_27 AT_20 5
AT_27 AT_21 5
AT_27 AT_22 5
AT_27 AT_23 5
AT_27 AT_24 5
AT_27 AT_25 5
AT_27 AT_26 5
AT_27 AT_27 0
AT_27 AT_28 5
AT_27 BA_20 10
AT_27 BA_22 10
AT_27 BA_25 10
AT_27 BA_26 10
AT_27 BA_27 10
AT_28 AT_20 5
AT_28 AT_21 5
AT_28 AT_22 5
AT_28 AT_23 5
AT_28 AT_24 5
AT_28 AT_25 5
AT_28 AT_26 5
AT_28 AT_27 5
AT_28 AT_28 0
AT_28 BA_20 10
AT_28 BA_22 10
AT_28 BA_25 10
AT_28 BA_26 10
AT_28 BA_27 10
BA_20 AT_20 10
BA_20 AT_21 10
BA_20 AT_22 10
BA_20 AT_23 10
BA_20 AT_24 10
BA_20 AT_25 10
BA_20 AT_26 10
BA_20 AT_27 10
BA_20 AT_28 10
BA_20 BA_20 0
BA_20 BA_22 5
BA_20 BA_25 5
BA_20 BA_26 5
BA_20 BA_27 5
BA_22 AT_20 10
BA_22 AT_21 10
BA_22 AT_22 10
BA_22 AT_23 10
BA_22 AT_24 10
BA_22 AT_25 10
BA_22 AT_26 10
BA_22 AT_27 10
BA_22 AT_28 10
BA_22 BA_20 5
BA_22 BA_22 0
BA_22 BA_25 5
BA_22 BA_26 5
BA_22 BA_27 5
BA_25 AT_20 10
BA_25 AT_21 10
BA_25 AT_22 10
BA_25 AT_23 10
BA_25 AT_24 10
BA_25 AT_25 10
BA_25 AT_26 10
BA_25 AT_27 10
BA_25 AT_28 10
BA_25 BA_20 5
BA_25 BA_22 5
BA_25 BA_25 0
BA_25 BA_26 5
BA_25 BA_27 5
BA_26 AT_20 10
BA_26 AT_21 10
BA_26 AT_22 10
BA_26 AT_23 10
BA_26 AT_24 10
BA_26 AT_25 10
BA_26 AT_26 10
BA_26 AT_27 10
BA_26 AT_28 10
BA_26 BA_20 5
BA_26 BA_22 5
BA_26 BA_25 5
BA_26 BA_26 0
BA_26 BA_27 5
BA_27 AT_20 10
BA_27 AT_21 10
BA_27 AT_22 10
BA_27 AT_23 10
BA_27 AT_24 10
BA_27 AT_25 10
BA_27 AT_26 10
BA_27 AT_27 10
BA_27 AT_28 10
BA_27 BA_20 5
BA_27 BA_22 5
BA_27 BA_25 5
BA_27 BA_26 5
BA_27 BA_27 0

 

?
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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
WITH W_NAME AS
    (
    SELECT 'AP001-1' AS "업체코드", 'AT_20' AS "아이템", '1' AS "아이템타입", '1' AS "작업주", 'AT' AS "아이템그룹", 10 "용량", TO_DATE('20190401','YYYYMMDD') AS "납품일", 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP001-2', 'AT_20' AS "아이템", '2' AS "아이템타입", '1' AS "작업주", 'AT' AS "아이템그룹", 20 "용량", TO_DATE('20190401','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP001-0', 'AT_20' AS "아이템", '0' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 5 "용량", TO_DATE('20190402','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP003-1', 'AT_21' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 18 "용량", TO_DATE('20190403','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP004-1', 'AT_22' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 54 "용량", TO_DATE('20190402','YYYYMMDD'), 'C' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP005-1', 'AT_23' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 8 "용량", TO_DATE('20190402','YYYYMMDD'), 'C' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP006-1', 'BA_20' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'BA' AS "아이템그룹", 21 "용량", TO_DATE('20190402','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP007-1', 'BA_22' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'BA' AS "아이템그룹", 41 "용량", TO_DATE('20190402','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP007-2', 'AT_22' AS "아이템", '2' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 13 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP007-3', 'AT_24' AS "아이템", '3' AS "아이템타입", '3' AS "작업주", 'AT' AS "아이템그룹", 1 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP007-4', 'AT_25' AS "아이템", '4' AS "아이템타입", '3' AS "작업주", 'AT' AS "아이템그룹", 2 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP001-1', 'AT_25' AS "아이템", '1' AS "아이템타입", '3' AS "작업주", 'AT' AS "아이템그룹", 3 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP001-2', 'AT_26' AS "아이템", '2' AS "아이템타입", '3' AS "작업주", 'AT' AS "아이템그룹", 4 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP002-1', 'AT_26' AS "아이템", '1' AS "아이템타입", '4' AS "작업주", 'AT' AS "아이템그룹", 7 "용량", TO_DATE('20190405','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP003-1', 'BA_27' AS "아이템", '1' AS "아이템타입", '4' AS "작업주", 'BA' AS "아이템그룹", 22 "용량", TO_DATE('20190405','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP004-1', 'AT_27' AS "아이템", '1' AS "아이템타입", '5' AS "작업주", 'AT' AS "아이템그룹", 26 "용량", TO_DATE('20190406','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP005-0', 'AT_28' AS "아이템", '0' AS "아이템타입", '5' AS "작업주", 'AT' AS "아이템그룹", 1 "용량", TO_DATE('20190407','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    )
,   W_TIME AS
    (
    SELECT 'AT_20' FROM_ITEM,   'AT_20' TO_ITEM, 0 AS S_TIME  FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM, 'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'AT_21' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM, 'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'AT_22' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM, 'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'AT_23' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM, 'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'AT_24' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM, 'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'AT_25' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM, 'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'AT_26' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM, 'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'AT_27' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM, 'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'AT_28' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM, 'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'BA_20' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'BA_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'BA_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'BA_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM, 'BA_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'BA_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'BA_22' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'BA_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'BA_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM, 'BA_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'BA_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'BA_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'BA_25' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'BA_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM, 'BA_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'BA_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'BA_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'BA_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'BA_26' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM, 'BA_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'BA_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'BA_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'BA_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'BA_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM, 'BA_27' TO_ITEM, 0    FROM DUAL
    )

 

by 홍길덩이 [2019.04.16 16:53:21]

질문에 있는 표와 WITH 문도 수정하여 다시 올렸습니다.

by 마농 [2019.04.17 16:22:04]
?
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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
WITH w_name AS
(
SELECT 'AP001-1' 업체코드, 'AT_20' 아이템, '1' 아이템타입, '1' 작업주, 'AT' 아이템그룹, 10 용량, '20190401' 납품일, 'A' 작업구역 FROM dual
UNION ALL SELECT 'AP001-2', 'AT_20', '2', '1', 'AT', 20, '20190401', 'A' FROM dual
UNION ALL SELECT 'AP001-0', 'AT_20', '0', '2', 'AT',  5, '20190402', 'A' FROM dual
UNION ALL SELECT 'AP003-1', 'AT_21', '1', '2', 'AT', 18, '20190403', 'A' FROM dual
UNION ALL SELECT 'AP004-1', 'AT_22', '1', '2', 'AT', 54, '20190402', 'C' FROM dual
UNION ALL SELECT 'AP005-1', 'AT_23', '1', '2', 'AT',  8, '20190402', 'C' FROM dual
UNION ALL SELECT 'AP006-1', 'BA_20', '1', '2', 'BA', 21, '20190402', 'A' FROM dual
UNION ALL SELECT 'AP007-1', 'BA_22', '1', '2', 'BA', 41, '20190402', 'A' FROM dual
UNION ALL SELECT 'AP007-2', 'AT_22', '2', '2', 'AT', 13, '20190404', 'A' FROM dual
UNION ALL SELECT 'AP007-3', 'AT_24', '3', '3', 'AT',  1, '20190404', 'A' FROM dual
UNION ALL SELECT 'AP007-4', 'AT_25', '4', '3', 'AT',  2, '20190404', 'A' FROM dual
UNION ALL SELECT 'AP001-1', 'AT_25', '1', '3', 'AT',  3, '20190404', 'A' FROM dual
UNION ALL SELECT 'AP001-2', 'AT_26', '2', '3', 'AT',  4, '20190404', 'A' FROM dual
UNION ALL SELECT 'AP002-1', 'AT_26', '1', '4', 'AT',  7, '20190405', 'A' FROM dual
UNION ALL SELECT 'AP003-1', 'BA_27', '1', '4', 'BA', 22, '20190405', 'A' FROM dual
UNION ALL SELECT 'AP004-1', 'AT_27', '1', '5', 'AT', 26, '20190406', 'A' FROM dual
UNION ALL SELECT 'AP005-0', 'AT_28', '0', '5', 'AT',  1, '20190407', 'A' FROM dual
)
, w_time AS
(
SELECT 'AT_20' from_item, 'AT_20' to_item, 0 s_time FROM dual
UNION ALL SELECT 'AT_20', 'AT_21',  5 FROM dual
UNION ALL SELECT 'AT_20', 'AT_22',  5 FROM dual
UNION ALL SELECT 'AT_20', 'AT_23',  5 FROM dual
UNION ALL SELECT 'AT_20', 'AT_24',  5 FROM dual
UNION ALL SELECT 'AT_20', 'AT_25',  5 FROM dual
UNION ALL SELECT 'AT_20', 'AT_26',  5 FROM dual
UNION ALL SELECT 'AT_20', 'AT_27',  5 FROM dual
UNION ALL SELECT 'AT_20', 'AT_28',  5 FROM dual
UNION ALL SELECT 'AT_20', 'BA_20', 10 FROM dual
UNION ALL SELECT 'AT_20', 'BA_22', 10 FROM dual
UNION ALL SELECT 'AT_20', 'BA_25', 10 FROM dual
UNION ALL SELECT 'AT_20', 'BA_26', 10 FROM dual
UNION ALL SELECT 'AT_20', 'BA_27', 10 FROM dual
UNION ALL SELECT 'AT_21', 'AT_20',  5 FROM dual
UNION ALL SELECT 'AT_21', 'AT_21',  0 FROM dual
UNION ALL SELECT 'AT_21', 'AT_22',  5 FROM dual
UNION ALL SELECT 'AT_21', 'AT_23',  5 FROM dual
UNION ALL SELECT 'AT_21', 'AT_24',  5 FROM dual
UNION ALL SELECT 'AT_21', 'AT_25',  5 FROM dual
UNION ALL SELECT 'AT_21', 'AT_26',  5 FROM dual
UNION ALL SELECT 'AT_21', 'AT_27',  5 FROM dual
UNION ALL SELECT 'AT_21', 'AT_28',  5 FROM dual
UNION ALL SELECT 'AT_21', 'BA_20', 10 FROM dual
UNION ALL SELECT 'AT_21', 'BA_22', 10 FROM dual
UNION ALL SELECT 'AT_21', 'BA_25', 10 FROM dual
UNION ALL SELECT 'AT_21', 'BA_26', 10 FROM dual
UNION ALL SELECT 'AT_21', 'BA_27', 10 FROM dual
UNION ALL SELECT 'AT_22', 'AT_20',  5 FROM dual
UNION ALL SELECT 'AT_22', 'AT_21',  5 FROM dual
UNION ALL SELECT 'AT_22', 'AT_22',  0 FROM dual
UNION ALL SELECT 'AT_22', 'AT_23',  5 FROM dual
UNION ALL SELECT 'AT_22', 'AT_24',  5 FROM dual
UNION ALL SELECT 'AT_22', 'AT_25',  5 FROM dual
UNION ALL SELECT 'AT_22', 'AT_26',  5 FROM dual
UNION ALL SELECT 'AT_22', 'AT_27',  5 FROM dual
UNION ALL SELECT 'AT_22', 'AT_28',  5 FROM dual
UNION ALL SELECT 'AT_22', 'BA_20', 10 FROM dual
UNION ALL SELECT 'AT_22', 'BA_22', 10 FROM dual
UNION ALL SELECT 'AT_22', 'BA_25', 10 FROM dual
UNION ALL SELECT 'AT_22', 'BA_26', 10 FROM dual
UNION ALL SELECT 'AT_22', 'BA_27', 10 FROM dual
UNION ALL SELECT 'AT_23', 'AT_20',  5 FROM dual
UNION ALL SELECT 'AT_23', 'AT_21',  5 FROM dual
UNION ALL SELECT 'AT_23', 'AT_22',  5 FROM dual
UNION ALL SELECT 'AT_23', 'AT_23',  0 FROM dual
UNION ALL SELECT 'AT_23', 'AT_24',  5 FROM dual
UNION ALL SELECT 'AT_23', 'AT_25',  5 FROM dual
UNION ALL SELECT 'AT_23', 'AT_26',  5 FROM dual
UNION ALL SELECT 'AT_23', 'AT_27',  5 FROM dual
UNION ALL SELECT 'AT_23', 'AT_28',  5 FROM dual
UNION ALL SELECT 'AT_23', 'BA_20', 10 FROM dual
UNION ALL SELECT 'AT_23', 'BA_22', 10 FROM dual
UNION ALL SELECT 'AT_23', 'BA_25', 10 FROM dual
UNION ALL SELECT 'AT_23', 'BA_26', 10 FROM dual
UNION ALL SELECT 'AT_23', 'BA_27', 10 FROM dual
UNION ALL SELECT 'AT_24', 'AT_20',  5 FROM dual
UNION ALL SELECT 'AT_24', 'AT_21',  5 FROM dual
UNION ALL SELECT 'AT_24', 'AT_22',  5 FROM dual
UNION ALL SELECT 'AT_24', 'AT_23',  5 FROM dual
UNION ALL SELECT 'AT_24', 'AT_24',  0 FROM dual
UNION ALL SELECT 'AT_24', 'AT_25',  5 FROM dual
UNION ALL SELECT 'AT_24', 'AT_26',  5 FROM dual
UNION ALL SELECT 'AT_24', 'AT_27',  5 FROM dual
UNION ALL SELECT 'AT_24', 'AT_28',  5 FROM dual
UNION ALL SELECT 'AT_24', 'BA_20', 10 FROM dual
UNION ALL SELECT 'AT_24', 'BA_22', 10 FROM dual
UNION ALL SELECT 'AT_24', 'BA_25', 10 FROM dual
UNION ALL SELECT 'AT_24', 'BA_26', 10 FROM dual
UNION ALL SELECT 'AT_24', 'BA_27', 10 FROM dual
UNION ALL SELECT 'AT_25', 'AT_20',  5 FROM dual
UNION ALL SELECT 'AT_25', 'AT_21',  5 FROM dual
UNION ALL SELECT 'AT_25', 'AT_22',  5 FROM dual
UNION ALL SELECT 'AT_25', 'AT_23',  5 FROM dual
UNION ALL SELECT 'AT_25', 'AT_24',  5 FROM dual
UNION ALL SELECT 'AT_25', 'AT_25',  0 FROM dual
UNION ALL SELECT 'AT_25', 'AT_26',  5 FROM dual
UNION ALL SELECT 'AT_25', 'AT_27',  5 FROM dual
UNION ALL SELECT 'AT_25', 'AT_28',  5 FROM dual
UNION ALL SELECT 'AT_25', 'BA_20', 10 FROM dual
UNION ALL SELECT 'AT_25', 'BA_22', 10 FROM dual
UNION ALL SELECT 'AT_25', 'BA_25', 10 FROM dual
UNION ALL SELECT 'AT_25', 'BA_26', 10 FROM dual
UNION ALL SELECT 'AT_25', 'BA_27', 10 FROM dual
UNION ALL SELECT 'AT_26', 'AT_20',  5 FROM dual
UNION ALL SELECT 'AT_26', 'AT_21',  5 FROM dual
UNION ALL SELECT 'AT_26', 'AT_22',  5 FROM dual
UNION ALL SELECT 'AT_26', 'AT_23',  5 FROM dual
UNION ALL SELECT 'AT_26', 'AT_24',  5 FROM dual
UNION ALL SELECT 'AT_26', 'AT_25',  5 FROM dual
UNION ALL SELECT 'AT_26', 'AT_26',  0 FROM dual
UNION ALL SELECT 'AT_26', 'AT_27',  5 FROM dual
UNION ALL SELECT 'AT_26', 'AT_28',  5 FROM dual
UNION ALL SELECT 'AT_26', 'BA_20', 10 FROM dual
UNION ALL SELECT 'AT_26', 'BA_22', 10 FROM dual
UNION ALL SELECT 'AT_26', 'BA_25', 10 FROM dual
UNION ALL SELECT 'AT_26', 'BA_26', 10 FROM dual
UNION ALL SELECT 'AT_26', 'BA_27', 10 FROM dual
UNION ALL SELECT 'AT_27', 'AT_20',  5 FROM dual
UNION ALL SELECT 'AT_27', 'AT_21',  5 FROM dual
UNION ALL SELECT 'AT_27', 'AT_22',  5 FROM dual
UNION ALL SELECT 'AT_27', 'AT_23',  5 FROM dual
UNION ALL SELECT 'AT_27', 'AT_24',  5 FROM dual
UNION ALL SELECT 'AT_27', 'AT_25',  5 FROM dual
UNION ALL SELECT 'AT_27', 'AT_26',  5 FROM dual
UNION ALL SELECT 'AT_27', 'AT_27',  0 FROM dual
UNION ALL SELECT 'AT_27', 'AT_28',  5 FROM dual
UNION ALL SELECT 'AT_27', 'BA_20', 10 FROM dual
UNION ALL SELECT 'AT_27', 'BA_22', 10 FROM dual
UNION ALL SELECT 'AT_27', 'BA_25', 10 FROM dual
UNION ALL SELECT 'AT_27', 'BA_26', 10 FROM dual
UNION ALL SELECT 'AT_27', 'BA_27', 10 FROM dual
UNION ALL SELECT 'AT_28', 'AT_20',  5 FROM dual
UNION ALL SELECT 'AT_28', 'AT_21',  5 FROM dual
UNION ALL SELECT 'AT_28', 'AT_22',  5 FROM dual
UNION ALL SELECT 'AT_28', 'AT_23',  5 FROM dual
UNION ALL SELECT 'AT_28', 'AT_24',  5 FROM dual
UNION ALL SELECT 'AT_28', 'AT_25',  5 FROM dual
UNION ALL SELECT 'AT_28', 'AT_26',  5 FROM dual
UNION ALL SELECT 'AT_28', 'AT_27',  5 FROM dual
UNION ALL SELECT 'AT_28', 'AT_28',  0 FROM dual
UNION ALL SELECT 'AT_28', 'BA_20', 10 FROM dual
UNION ALL SELECT 'AT_28', 'BA_22', 10 FROM dual
UNION ALL SELECT 'AT_28', 'BA_25', 10 FROM dual
UNION ALL SELECT 'AT_28', 'BA_26', 10 FROM dual
UNION ALL SELECT 'AT_28', 'BA_27', 10 FROM dual
UNION ALL SELECT 'BA_20', 'AT_20', 10 FROM dual
UNION ALL SELECT 'BA_20', 'AT_21', 10 FROM dual
UNION ALL SELECT 'BA_20', 'AT_22', 10 FROM dual
UNION ALL SELECT 'BA_20', 'AT_23', 10 FROM dual
UNION ALL SELECT 'BA_20', 'AT_24', 10 FROM dual
UNION ALL SELECT 'BA_20', 'AT_25', 10 FROM dual
UNION ALL SELECT 'BA_20', 'AT_26', 10 FROM dual
UNION ALL SELECT 'BA_20', 'AT_27', 10 FROM dual
UNION ALL SELECT 'BA_20', 'AT_28', 10 FROM dual
UNION ALL SELECT 'BA_20', 'BA_20',  0 FROM dual
UNION ALL SELECT 'BA_20', 'BA_22',  5 FROM dual
UNION ALL SELECT 'BA_20', 'BA_25',  5 FROM dual
UNION ALL SELECT 'BA_20', 'BA_26',  5 FROM dual
UNION ALL SELECT 'BA_20', 'BA_27',  5 FROM dual
UNION ALL SELECT 'BA_22', 'AT_20', 10 FROM dual
UNION ALL SELECT 'BA_22', 'AT_21', 10 FROM dual
UNION ALL SELECT 'BA_22', 'AT_22', 10 FROM dual
UNION ALL SELECT 'BA_22', 'AT_23', 10 FROM dual
UNION ALL SELECT 'BA_22', 'AT_24', 10 FROM dual
UNION ALL SELECT 'BA_22', 'AT_25', 10 FROM dual
UNION ALL SELECT 'BA_22', 'AT_26', 10 FROM dual
UNION ALL SELECT 'BA_22', 'AT_27', 10 FROM dual
UNION ALL SELECT 'BA_22', 'AT_28', 10 FROM dual
UNION ALL SELECT 'BA_22', 'BA_20',  5 FROM dual
UNION ALL SELECT 'BA_22', 'BA_22',  0 FROM dual
UNION ALL SELECT 'BA_22', 'BA_25',  5 FROM dual
UNION ALL SELECT 'BA_22', 'BA_26',  5 FROM dual
UNION ALL SELECT 'BA_22', 'BA_27',  5 FROM dual
UNION ALL SELECT 'BA_25', 'AT_20', 10 FROM dual
UNION ALL SELECT 'BA_25', 'AT_21', 10 FROM dual
UNION ALL SELECT 'BA_25', 'AT_22', 10 FROM dual
UNION ALL SELECT 'BA_25', 'AT_23', 10 FROM dual
UNION ALL SELECT 'BA_25', 'AT_24', 10 FROM dual
UNION ALL SELECT 'BA_25', 'AT_25', 10 FROM dual
UNION ALL SELECT 'BA_25', 'AT_26', 10 FROM dual
UNION ALL SELECT 'BA_25', 'AT_27', 10 FROM dual
UNION ALL SELECT 'BA_25', 'AT_28', 10 FROM dual
UNION ALL SELECT 'BA_25', 'BA_20',  5 FROM dual
UNION ALL SELECT 'BA_25', 'BA_22',  5 FROM dual
UNION ALL SELECT 'BA_25', 'BA_25',  0 FROM dual
UNION ALL SELECT 'BA_25', 'BA_26',  5 FROM dual
UNION ALL SELECT 'BA_25', 'BA_27',  5 FROM dual
UNION ALL SELECT 'BA_26', 'AT_20', 10 FROM dual
UNION ALL SELECT 'BA_26', 'AT_21', 10 FROM dual
UNION ALL SELECT 'BA_26', 'AT_22', 10 FROM dual
UNION ALL SELECT 'BA_26', 'AT_23', 10 FROM dual
UNION ALL SELECT 'BA_26', 'AT_24', 10 FROM dual
UNION ALL SELECT 'BA_26', 'AT_25', 10 FROM dual
UNION ALL SELECT 'BA_26', 'AT_26', 10 FROM dual
UNION ALL SELECT 'BA_26', 'AT_27', 10 FROM dual
UNION ALL SELECT 'BA_26', 'AT_28', 10 FROM dual
UNION ALL SELECT 'BA_26', 'BA_20',  5 FROM dual
UNION ALL SELECT 'BA_26', 'BA_22',  5 FROM dual
UNION ALL SELECT 'BA_26', 'BA_25',  5 FROM dual
UNION ALL SELECT 'BA_26', 'BA_26',  0 FROM dual
UNION ALL SELECT 'BA_26', 'BA_27',  5 FROM dual
UNION ALL SELECT 'BA_27', 'AT_20', 10 FROM dual
UNION ALL SELECT 'BA_27', 'AT_21', 10 FROM dual
UNION ALL SELECT 'BA_27', 'AT_22', 10 FROM dual
UNION ALL SELECT 'BA_27', 'AT_23', 10 FROM dual
UNION ALL SELECT 'BA_27', 'AT_24', 10 FROM dual
UNION ALL SELECT 'BA_27', 'AT_25', 10 FROM dual
UNION ALL SELECT 'BA_27', 'AT_26', 10 FROM dual
UNION ALL SELECT 'BA_27', 'AT_27', 10 FROM dual
UNION ALL SELECT 'BA_27', 'AT_28', 10 FROM dual
UNION ALL SELECT 'BA_27', 'BA_20',  5 FROM dual
UNION ALL SELECT 'BA_27', 'BA_22',  5 FROM dual
UNION ALL SELECT 'BA_27', 'BA_25',  5 FROM dual
UNION ALL SELECT 'BA_27', 'BA_26',  5 FROM dual
UNION ALL SELECT 'BA_27', 'BA_27',  0 FROM dual
)
, w_name1 AS
(
SELECT 업체코드, 아이템, 아이템타입, 작업주, 아이템그룹, 용량, 납품일, 작업구역
     , ROW_NUMBER() OVER(PARTITION BY 작업구역
                             ORDER BY 작업주, 아이템그룹, 납품일, SIGN(아이템타입), 용량 DESC, 아이템) rn1
     , ROW_NUMBER() OVER(PARTITION BY 아이템
                             ORDER BY 작업주, 아이템그룹, 납품일, SIGN(아이템타입), 용량 DESC) rn
  FROM w_name
)
, w_time1 AS
(
SELECT a.from_item
     , b.rn from_rn
     , c.작업구역
     , a.to_item
     , c.rn to_rn
     , c.납품일
     , a.s_time
     , c.용량
  FROM w_time a
     , w_name1 b
     , w_name1 c
 WHERE a.from_item = b.아이템
   AND a.to_item   = c.아이템
)
, w_temp1(업체코드, 아이템, 아이템타입, 작업주, 아이템그룹, 용량, 납품일, 작업구역, rn, lv, pp, to_item_rn) AS
(
SELECT a.업체코드, a.아이템, a.아이템타입, a.작업주, a.아이템그룹, a.용량, a.납품일, a.작업구역
     , a.rn
     , 1 lv
     , CAST('>'|| a.아이템 ||'/'|| a.rn AS VARCHAR2(999)) pp
     , (SELECT MIN(c.아이템 ||'/'|| c.rn) KEEP(DENSE_RANK FIRST
               ORDER BY c.작업주, b.s_time, c.아이템그룹, c.납품일, SIGN(c.아이템타입), c.용량 DESC, c.아이템)
          FROM w_time1 b
             , w_name1 c
         WHERE b.from_item = a.아이템
           AND b.from_rn   = a.rn
           AND b.to_item   = c.아이템
           AND b.to_rn     = c.rn
           AND c.작업구역  = a.작업구역
           AND c.rn1      != 1
        ) to_item_rn
  FROM w_name1 a
 WHERE rn1 = 1
 UNION ALL
SELECT a.업체코드, a.아이템, a.아이템타입, a.작업주, a.아이템그룹, a.용량, a.납품일, a.작업구역
     , a.rn
     , d.lv + 1 lv
     , d.pp ||'>'|| a.아이템 ||'/'|| a.rn pp
     , (SELECT MIN(c.아이템 ||'/'|| c.rn) KEEP(DENSE_RANK FIRST
               ORDER BY c.작업주, b.s_time, c.아이템그룹, c.납품일, SIGN(c.아이템타입), c.용량 DESC, c.아이템)
          FROM w_time1 b
             , w_name1 c
         WHERE b.from_item = a.아이템
           AND b.from_rn   = a.rn
           AND b.to_item   = c.아이템
           AND b.to_rn     = c.rn
           AND c.작업구역  = a.작업구역
           AND INSTR(d.pp||'>'|| a.아이템 ||'/'|| a.rn, '>'||c.아이템||'/'||c.rn) = 0
        ) to_item_rn
  FROM w_name1 a
     , w_temp1 d
 WHERE a.작업구역 = d.작업구역
   AND a.아이템||'/'||a.rn = d.to_item_rn
   AND INSTR(d.pp||'>', '>'||a.아이템||'/'||a.rn||'>') = 0
)
SELECT 업체코드, 아이템, 아이템타입, 작업주, 아이템그룹, 용량, 납품일, 작업구역
     , DENSE_RANK() OVER(ORDER BY 작업주, rn) 순번
  FROM (SELECT 업체코드, 아이템, 아이템타입, 작업주, 아이템그룹, 용량, 납품일, 작업구역
             , lv
             , ROW_NUMBER() OVER(PARTITION BY 작업주, 작업구역 ORDER BY lv) rn
          FROM w_temp1
        )
 ORDER BY 작업주, rn, 작업구역
;

 

by 홍길덩이 [2019.04.18 16:10:14]

감사합니다~

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