데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

Oracle Q&A 40304 게시물 읽기

No. 40304

CONNECT BY 쿼리문 좀 도와주세요
작성자	김정훈(큐니백작)	작성일	2013-11-07 16:02	조회수	6,820

쿼리 작성에 애를 먹고 있습니다.
좀 도와주세요

ID SEQ SUB_ID SUB_SEQ
-----------------------------------------------------------------
A 1 B 56
B 56 C 2
B 56 C 3
C 2 D 10
C 3 D 18
C 4 D 10
C 10 D 10
D 10 E 4
D 10 E 1
D 18 E 9
D 18 E 5

와 같이 데이터가 있을 경우

SELECT REGEXP_SUBSTR( ID, '[^,]+', 1, 1) v1,
       REGEXP_SUBSTR( ID, '[^,]+', 1, 2) v2,
       REGEXP_SUBSTR( ID, '[^,]+', 1, 3) v3,
       REGEXP_SUBSTR( ID, '[^,]+', 1, 4) v4
FROM (
   SELECT SYS_CONNECT_BY_PATH(SUB_ID || TO_CHAR(SUB_SEQ, '00'), ',') ID,
              ROWNUM RN
           FROM TMP
           START WITH ID = 'A' AND SEQ = 1
           CONNECT BY PRIOR SUB_ID = ID AND SUB_SEQ = SEQ
        ) A
ORDER BY A.RN

으로 조회하니 조회가 안되고 AND SUB_SEQ = SEQ 이부분을 빼면 조회가 이상하게 됩니다.
CONNECT BY PRIOR SUB_ID||TO_CHAR(SUB_SEQ) = ID||TO_CHAR(SEQ) 로 하면 조회시간이 엄청납니다.
데이터가 13,000,000 정도 있는 테이블이라....

C 4 D 10
C 10 D 10
이부분도 포함되어 나오는 것 갔습니다

최종 결과는 아래와 같으면 좋겠습니다.

V0 V1 V2 V3 V4
-----------------------------------------
A 01 B 56
A 01 B 56 C 02
A 01 B 56 C 02 D 10
A 01 B 56 C 02 D 10 E 04
A 01 B 56 C 02 D 10 E 01
A 01 B 56 C 02 D 10 E 09
A 01 B 56 C 02 D 10 E 05
A 01 B 56 C 02 D 18
A 01 B 56 C 02 D 18 E 04
A 01 B 56 C 02 D 18 E 01
A 01 B 56 C 02 D 18 E 09
A 01 B 56 C 02 D 18 E 05
A 01 B 56 C 03
A 01 B 56 C 03 D 10
A 01 B 56 C 03 D 10 E 04
A 01 B 56 C 03 D 10 E 01
A 01 B 56 C 03 D 10 E 09
A 01 B 56 C 03 D 10 E 05
A 01 B 56 C 03 D 18
A 01 B 56 C 03 D 18 E 04
A 01 B 56 C 03 D 18 E 01
A 01 B 56 C 03 D 18 E 09
A 01 B 56 C 03 D 18 E 05

이 글에 대한 댓글이 총 6건 있습니다.

텍스트를 복사했더니 탭이 다 사라지고 붙어버렸네요....죄송

김정훈(큐니백작)님이 2013-11-07 16:03에 작성한 댓글입니다.

틀린 부분만 노랑색 표시 했습니다.

with tmp (ID, SEQ, SUB_ID, SUB_SEQ ) as (

SELECT 'A', 1 ,'B' ,56 from dual

UNION ALL SELECT 'B', 56, 'C', 2 from dual

UNION ALL SELECT 'B', 56, 'C', 3 from dual

UNION ALL SELECT 'C', 2 ,'D' ,10 from dual

UNION ALL SELECT 'C', 3 ,'D' ,18 from dual

UNION ALL SELECT 'C', 4 ,'D' ,10 from dual

UNION ALL SELECT 'C', 10, 'D', 10 from dual

UNION ALL SELECT 'D', 10, 'E', 4 from dual

UNION ALL SELECT 'D', 10, 'E', 1 from dual

UNION ALL SELECT 'D', 18, 'E', 9 from dual

UNION ALL SELECT 'D', 18, 'E', 5 from dual

)

SELECT

REGEXP_SUBSTR( ID, '[^,]+', 1, 1) v1,

REGEXP_SUBSTR( ID, '[^,]+', 1, 2) v2,

REGEXP_SUBSTR( ID, '[^,]+', 1, 3) v3,

REGEXP_SUBSTR( ID, '[^,]+', 1, 4) v4

FROM (

SELECT SYS_CONNECT_BY_PATH(ID || TO_CHAR(SEQ, '00'), ',') ID,

ROWNUM RN

FROM TMP

START WITH ID = 'A' AND SEQ = 1

CONNECT BY PRIOR SUB_ID = ID AND PRIOR SUB_SEQ = SEQ

) A

ORDER BY A.RN;

tohappy(tohappy)님이 2013-11-07 16:14에 작성한 댓글입니다.

tohappy님 답변 감사합니다

그런데 REGEXP_SUBSTR( ID, '[^,]+', 1, 5) v5를 추가 해도 E부분은 나타나지 않네요?

확인 좀 부탁드립니다.

김정훈(큐니백작)님이 2013-11-07 16:21에 작성한 댓글입니다.

다시 수정 부분 노랑색 입니다.

: CONNECT_BY_ROOT 사용

SELECT
REGEXP_SUBSTR( ID, '[^,]+', 1, 1) v1,

REGEXP_SUBSTR( ID, '[^,]+', 1, 2) v2,

REGEXP_SUBSTR( ID, '[^,]+', 1, 3) v3,

REGEXP_SUBSTR( ID, '[^,]+', 1, 4) v4,

REGEXP_SUBSTR( ID, '[^,]+', 1, 5) v5

FROM (

SELECT CONNECT_BY_ROOT ID || CONNECT_BY_ROOT SEQ ||

SYS_CONNECT_BY_PATH(SUB_ID || TO_CHAR(SUB_SEQ, '00'), ',') ID,

ROWNUM RN

FROM TMP

START WITH ID = 'A' AND SEQ = 1

CONNECT BY PRIOR SUB_ID = ID AND PRIOR SUB_SEQ = SEQ

) A

ORDER BY A.RN

tohappy(tohappy)님이 2013-11-07 17:01에 작성한 댓글입니다.

SELECT REGEXP_SUBSTR(id, '[^,]+', 1, 1) v1
     , REGEXP_SUBSTR(id, '[^,]+', 1, 2) v2
     , REGEXP_SUBSTR(id, '[^,]+', 1, 3) v3
     , REGEXP_SUBSTR(id, '[^,]+', 1, 4) v4
     , REGEXP_SUBSTR(id, '[^,]+', 1, 5) v5
FROM (
        SELECT SYS_CONNECT_BY_PATH(id || TO_CHAR(seq, '00'), ',')
               ||','|| sub_id || TO_CHAR(sub_seq, '00') AS id
             , ROWNUM rn
          FROM tmp
         START WITH id = 'A'
                AND seq = 1
         CONNECT BY PRIOR sub_id = id
                AND PRIOR sub_seq = seq
        )
;

마농(manon94)님이 2013-11-07 17:01에 작성한 댓글입니다.

tohappy님, 마농님 답변 감사합니다.

덕분에 문제를 해결 하였습니다.

김정훈(큐니백작)님이 2013-11-07 17:06에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
40307	이런것도 쿼리로 가능할까요? ㅠㅠ [2]	햇살한조각	2013-11-08	6727
40306	UUID 생성 [1]	구박덩어리	2013-11-08	7741
40305	도로명 주소 관련 인덱스 생성 [1]	곽동엽	2013-11-08	11165
40304	CONNECT BY 쿼리문 좀 도와주세요 [6]	김정훈	2013-11-07	6820
40303	dmp파일 CMD창에서 import할때... [2]	한상원	2013-11-07	5953
40302	프로시저로 되어있는것 쿼리 추출할수 있을까요? [3]	궁금이	2013-11-06	6854
40301	엉뚱한 합계 구하기 [1]	궁금	2013-11-04	6507