데이터베이스 질문과 답변
by 최경수 [SQL Query] 계층쿼리 sys_connect_by_path [2014.06.23 18:41:39]
안녕하세요?
다음과 같은 BOM 구조를 지닌 형태를 A(parent)-B(child), B(parent)-C(child) → A-B-C
특정조건에 해당하여 B는 생략한채 A(parent)-C(child) 횡형태로 구성하고 싶습니다.
[BOM]
SELECT 'AAA' PARENT, 'BBB' CHILD, 'MAIN' STAGE FROM DUAL
UNION ALL SELECT 'BBB', 'CCC', 'SUB' STAGE FROM DUAL
[A-B-C형태 쿼리]
WITH T AS
(
SELECT A.*
FROM (
SELECT CONNECT_BY_ROOT PARENT AS ROOT
,LEVEL AS STEP
,SUBSTR(SYS_CONNECT_BY_PATH(A.PARENT || '@' || A.STAGE || '@','|') ||A.CHILD , 2) PATH --, A.PARENT, A.STAGE
FROM (
SELECT 'AAA' PARENT, 'BBB' CHILD, 'MAIN' STAGE FROM DUAL
UNION ALL SELECT 'BBB', 'CCC', 'SUB' STAGE FROM DUAL
) A
WHERE CONNECT_BY_ISLEAF = 1
START WITH (A.STAGE, A.PARENT) IN (
SELECT DISTINCT A.STAGE, A.PARENT
FROM (
SELECT 'AAA' PARENT, 'BBB' CHILD, 'MAIN' STAGE FROM DUAL
UNION ALL SELECT 'BBB', 'CCC', 'SUB' STAGE FROM DUAL
) A
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR PARENT = CHILD
)
CONNECT BY PRIOR CHILD = PARENT
) A
)
SELECT ROOT, STEP, PATH,
REGEXP_SUBSTR(PATH,'[^@|]+',1,1) AS ONELEVEL,
--REGEXP_SUBSTR(PATH,'[^@|]+',1,2),
REGEXP_SUBSTR(PATH,'[^@|]+',1,3) AS TWOLEVEL,
--REGEXP_SUBSTR(PATH,'[^@|]+',1,4),
REGEXP_SUBSTR(PATH,'[^@|]+',1,5) AS THREELEVEL
--REGEXP_SUBSTR(PATH,'[^@|]+',1,6),
--REGEXP_SUBSTR(PATH,'[^@|]+',1,7) AS FOURLEVEL,
--REGEXP_SUBSTR(PATH,'[^@|]+',1,8)
--REGEXP_SUBSTR(PATH,'[^@|]+',1,9) AS FIVELEVEL
FROM T
어떻게 해야 SYS_CONNECT_BY_PATH 결과 값을 사용하여
중간공정인 B를 제외하고 최상위 A와 최하위 C를 연결 시킬 수 있을까요?
많은 조언 부탁드립니다.
특정조건에 해당하여 B는 생략한채??? 라고 하셨는데요?
특정조건에 뭔지에 대한 자세한 설명을 해주세요.
예시 자료도 하나 뿐인데...좀더 다양한 예시가 있으면 좋겠네요.
답변감사드립니다.
조금 더 구체적인 예와 특정조건 붙여보겠습니다.
[BOM형태]
(
SELECT 'AAA' parent, 'BBB' child, 'MAIN' stage FROM dual
UNION ALL SELECT 'BBB', 'CCC', 'SUB' FROM dual
UNION ALL SELECT 'CCC', 'DDD', 'DUMMY' FROM dual
) UNION
(
SELECT 'AAA' parent, 'EEE' child, 'MAIN' stage FROM dual
UNION ALL SELECT 'EEE', 'FFF', 'MOTOR' FROM dual
UNION ALL SELECT 'FFF', 'EEE', 'SUB' FROM dual
UNION ALL SELECT 'EEE', 'HHH', 'SUB' FROM dual
)
[특정조건] :STAGE 값이 SUB인 경우는 BOM에서 제외함.
[결과값 예상] : 오른편에 STAGE 값이 'SUB'인 경우를 제외한 채 상,하위 연결값 Return
| PARENT | CHILD | STAGE | → | PATH | PARENT | CHILD1 | CHILD2 | CHILD3 |
| AAA | BBB | MAIN | AAA-CCC-DDD | AAA | CCC | DDD | ||
| BBB | CCC | SUB | AAA-EEE-GGG-HHH | AAA | EEE | GGG | HHH | |
| CCC | DDD | DUMMY | ||||||
| AAA | EEE | MAIN | ||||||
| EEE | FFF | MOTOR | ||||||
| FFF | GGG | SUB | ||||||
| GGG | HHH | INJ |
위와 같이 결과를 작성하기를 원합니다. 다시 한번 조언부탁드리겠습니다.
WITH t AS ( SELECT 'AAA' parent, 'BBB' child, 'MAIN' stage FROM dual UNION ALL SELECT 'BBB', 'CCC', 'SUB' FROM dual ) -- 단순하게 최상위와 최하위만 연결하고자 한다면... SELECT CONNECT_BY_ROOT parent || '-' || child path FROM t m WHERE CONNECT_BY_ISLEAF = 1 START WITH NOT EXISTS (SELECT 1 FROM t WHERE child = m.parent) CONNECT BY PRIOR child = parent ;
WITH t AS ( SELECT 'AAA' parent, 'BBB' child, 'MAIN' stage FROM dual UNION ALL SELECT 'BBB', 'CCC', 'SUB' FROM dual UNION ALL SELECT 'CCC', 'DDD', 'DUMMY' FROM dual UNION ALL SELECT 'AAA', 'EEE', 'MAIN' FROM dual UNION ALL SELECT 'EEE', 'FFF', 'MOTOR' FROM dual UNION ALL SELECT 'FFF', 'GGG', 'SUB' FROM dual UNION ALL SELECT 'GGG', 'HHH', 'INJ' FROM dual ) SELECT path , REGEXP_SUBSTR(path, '[^-]+', 1, 1) parent , REGEXP_SUBSTR(path, '[^-]+', 1, 2) child1 , REGEXP_SUBSTR(path, '[^-]+', 1, 3) child2 , REGEXP_SUBSTR(path, '[^-]+', 1, 4) child3 , REGEXP_SUBSTR(path, '[^-]+', 1, 5) child4 FROM (SELECT SUBSTR( REGEXP_REPLACE( SYS_CONNECT_BY_PATH(DECODE(stage, 'SUB', '', parent), '-') || DECODE(stage, 'SUB', '', '-'||child) , '-+', '-') , 2) path FROM t m WHERE CONNECT_BY_ISLEAF = 1 START WITH NOT EXISTS (SELECT 1 FROM t WHERE child = m.parent) CONNECT BY PRIOR child = parent ) ;
감사합니다.
쿼리 사용에 많은 도움됐습니다.
[BOM형태]
SELECT 'AAA' parent, 'BBB' child, 'MAIN' stage FROM dual
UNION ALL SELECT 'BBB', 'CCC', 'SUB' FROM dual
UNION ALL SELECT 'CCC', 'DDD', 'DUMMY' FROM dual
UNION ALL SELECT 'AAA', 'EEE', 'MAIN' FROM dual
UNION ALL SELECT 'EEE', 'FFF', 'MOTOR' FROM dual
UNION ALL SELECT 'FFF', 'GGG', 'SUB' FROM dual
UNION ALL SELECT 'GGG', 'HHH', 'INJ' FROM dual
UNION ALL SELECT 'HHH', 'III', 'DUMMY' FROM dual
한 가지가 막혀서 추가 질문드려도 될까요?
중간에 서브공정을 제외하고 위와 같이 상.하위 연결해서 출력이 됐습니다.
그러나 Layout을 아래와 같이 Parent-Chid1-Child2(3단계형태)로 만들어야할때,
AAA-EEE-GGG-HHH의 경우 공정조건이 MAIN-MOTOR-INJ, MAIN-MOTOR-DUMMY
(조건: 최하위 공정은 INJ, DUMMY이며 중간공정은 MOTOR이다.)
2가지로 나눠서 다음과 같이 출력하고자 합니다.
| PATH | PARENT | CHILD1 | CHILD2 |
| AAA-CCC-DDD | AAA | CCC | DDD |
| AAA-EEE-GGG | AAA | EEE | GGG |
| AAA-EEE-HHH | AAA | EEE | HHH |
다시 한 번 도움요청드립니다.
WITH t AS
(
SELECT 'AAA' parent, 'BBB' child, 'MAIN' stage FROM dual
UNION ALL SELECT 'BBB', 'CCC', 'SUB' FROM dual
UNION ALL SELECT 'CCC', 'DDD', 'DUMMY' FROM dual
UNION ALL SELECT 'AAA', 'EEE', 'MAIN' FROM dual
UNION ALL SELECT 'EEE', 'FFF', 'MOTOR' FROM dual
UNION ALL SELECT 'FFF', 'GGG', 'SUB' FROM dual
UNION ALL SELECT 'GGG', 'HHH', 'INJ' FROM dual
UNION ALL SELECT 'HHH', 'III', 'DUMMY' FROM dual
)
SELECT REGEXP_SUBSTR(x, '[^-]+', 1, 1) ||'-'||
REGEXP_SUBSTR(x, '[^-]+', 1, 2) ||'-'||
REGEXP_SUBSTR(x, '[^-]+', 1, 3) AS path
, REGEXP_SUBSTR(x, '[^-]+', 1, 1) p
, REGEXP_SUBSTR(x, '[^-]+', 1, 2) c1
, REGEXP_SUBSTR(x, '[^-]+', 1, 3) c2
FROM (SELECT REGEXP_REPLACE(
REVERSE(
SYS_CONNECT_BY_PATH(
CASE WHEN stage IN ('MAIN', 'MOTOR', CONNECT_BY_ROOT stage)
THEN REVERSE(parent) END
, '-')) || CONNECT_BY_ROOT child
, '-+', '-') x
FROM t m
WHERE CONNECT_BY_ISLEAF = 1
START WITH stage IN ('INJ', 'DUMMY')
CONNECT BY child = PRIOR parent
)
;
) 버튼을 클릭하여 작성 하시면 됩니다.