with t as (
select 0 lvl, 'A' item, 0 seq from dual union all
select 1, 'B', 1 from dual union all
select 2, 'C', 2 from dual union all
select 3, 'D', 3 from dual union all
select 3, 'E', 4 from dual union all
select 1, 'F', 5 from dual union all
select 2, 'G', 6 from dual union all
select 3, 'C', 7 from dual union all
select 4, 'D', 8 from dual union all
select 4, 'E', 9 from dual union all
select 1, 'H', 10 from dual union all
select 2, 'C', 11 from dual union all
select 3, 'D', 12 from dual union all
select 3, 'E', 13 from dual
)
원하는 결과내용
0 A 0
1 B 1
2 C 2
3 D 3
3 E 4
1 F 5
2 G 6
3 C 7
1 H 10
2 C 11
C자식으로 D,E가 있는데 C가 아래도 2번 사용되었습니다.
with t as (
select '-' parent, 'A' item, 0 seq from dual union all
select A, 'B', 1 from dual union all
select B, 'C', 2 from dual union all
select C, 'D', 3 from dual union all
select C, 'E', 4 from dual union all
select A, 'F', 5 from dual union all
select F, 'G', 6 from dual union all
select G, 'C', 7 from dual union all
select C, 'D', 8 from dual union all
select C, 'E', 9 from dual union all
select A, 'H', 10 from dual union all
select H, 'C', 11 from dual union all
select C, 'D', 12 from dual union all
select C, 'E', 13 from dual
)
질문 감사합니다.
원래 계층구조는 위와 같습니다. 이것을 받아서 migration을 합니다.
C-D와 C-E관계는 한번 생성되면 아래에서는 C만 가져와도 이미 D,E의 관계가 있으므로 별도의 생성이 필요하지 않아서 C-D와 C-E관계는 정보는 제거 하고 싶습니다. C를 제거하면 B-C, G-C, H-C정보를 알수 없으므로 삭제하지 않았습니다.
레벨과 Item관계는 해당 시스템에서 migration을 위해 지원하는 API format입니다.
모자관계에서 제거 작업이 되어도 됩니다.
with t as (
select 1 seq, '0' lvl, 'P3011-BOM' itemid, '' englishname, 'A.1' rev from dual union all
select 17, '3', 'F1000-P3011', '', 'A.1' from dual union all
select 18, '3|1', 'F1001-P3011', '', 'K.1' from dual union all
select 19, '3|1|1', '1000100XEC52', 'ENGINE ASSY', 'D.1' from dual union all
select 20, '3|1|2', '1000100XEC53', 'ENGINE ASSY', 'D.1' from dual union all
select 21, '3|1|3', '3708100AEC01', 'STARTER ASSY', 'B.1' from dual union all
select 22, '3|1|4', 'Q184U1060F70SE', 'HEX FLANGE BOLT', 'A.1' from dual union all
select 23, '3|1|5', 'Q184U10135F70SE', 'HEX FLANGE BOLT', 'A.1' from dual union all
select 24, '3|1|6', 'Q184U1080F70SE', 'HEX FLANGE BOLT', 'A.1' from dual union all
select 25, '3|1|7', 'Q184U1080F70SE', 'HEX FLANGE BOLT', 'A.1' from dual union all
select 26, '3|1|8', '1002019XEC61', 'HYDRAULIC TORQUE CONVERTER ASSY', 'A.1' from dual union all
select 27, '3|1|8|1', 'Q1840610FDE1', 'HEX FLANGE BOLT1', 'A.1' from dual union all
select 28, '3|1|8|2', 'Q1840610FDE2', 'HEX FLANGE BOLT2', 'A.1' from dual union all
select 164, '3|2', 'F1006-P3011', '', 'C.1' from dual union all
select 165, '3|2|1', '8402500XPW01A', 'HOOD ASSY,ENGINE TRIM', 'B.1' from dual union all
select 166, '3|2|1|1', '8402501XPW01A', 'HOOD,ENGINE TRIM', 'A.3' from dual union all
select 167, '3|2|1|2', '8402502XPW01A', 'HEAT SHIELD,ENGING TRIM HOOD', 'A.3' from dual union all
select 168, '3|2|1|3', '8402326-V08', 'GUM COVER', 'A.1' from dual union all
select 169, '3|2|1|4', '1002019XEC61', 'HYDRAULIC TORQUE CONVERTER ASSY', 'A.1' from dual union all
select 170, '3|2|1|4|1', 'Q1840610FDE1', 'HEX FLANGE BOLT1', 'A.1' from dual union all
select 171, '3|2|1|4|2', 'Q1840610FDE2', 'HEX FLANGE BOLT2', 'A.1' from dual
)
위에 그림에서 노란색 부분이 겹치는 부분입니다.
WITH t AS ( SELECT 1 seq, '0' lvl, 'P3011-BOM' itemid FROM dual UNION ALL SELECT 17, '3' , 'F1000-P3011' FROM dual UNION ALL SELECT 18, '3|1' , 'F1001-P3011' FROM dual UNION ALL SELECT 19, '3|1|1' , '1000100XEC52' FROM dual UNION ALL SELECT 20, '3|1|2' , '1000100XEC53' FROM dual UNION ALL SELECT 21, '3|1|3' , '3708100AEC01' FROM dual UNION ALL SELECT 22, '3|1|4' , 'Q184U1060F70SE' FROM dual UNION ALL SELECT 23, '3|1|5' , 'Q184U10135F70SE'FROM dual UNION ALL SELECT 24, '3|1|6' , 'Q184U1080F70SE' FROM dual UNION ALL SELECT 25, '3|1|7' , 'Q184U1080F70SE' FROM dual UNION ALL SELECT 26, '3|1|8' , '1002019XEC61' FROM dual UNION ALL SELECT 27, '3|1|8|1' , 'Q1840610FDE1' FROM dual UNION ALL SELECT 28, '3|1|8|2' , 'Q1840610FDE2' FROM dual UNION ALL SELECT 164, '3|2' , 'F1006-P3011' FROM dual UNION ALL SELECT 165, '3|2|1' , '8402500XPW01A' FROM dual UNION ALL SELECT 166, '3|2|1|1' , '8402501XPW01A' FROM dual UNION ALL SELECT 167, '3|2|1|2' , '8402502XPW01A' FROM dual UNION ALL SELECT 168, '3|2|1|3' , '8402326-V08' FROM dual UNION ALL SELECT 169, '3|2|1|4' , '1002019XEC61' FROM dual UNION ALL SELECT 170, '3|2|1|4|1', 'Q1840610FDE1' FROM dual UNION ALL SELECT 171, '3|2|1|4|2', 'Q1840610FDE2' FROM dual ) SELECT * FROM (SELECT a.seq, a.lvl, a.itemid , b.itemid p_itemid , ROW_NUMBER() OVER(PARTITION BY a.itemid, b.itemid ORDER BY a.seq) rn FROM t a , t b WHERE b.lvl(+) = SUBSTR(a.lvl, 1, INSTR(a.lvl, '|', -1)-1) ) WHERE rn = 1 ORDER BY seq ;