데이터베이스 질문과 답변
안녕하세요. 아래 주석으로 기재한 테이블처럼 쿼리를.. 짤 수 있을까요?
REL 테이블에 KEY와 NEXT_KEY에 값을 넣으면서 흐름도? 테이블을 만들어야하는데..
스크립트에서 처리를 해야할지 잘 모르겠습니다..ㅠ
WITH BASE AS(
SELECT '001' AS KEY, 'Math' AS KEY_NAME, 1 AS SECTION FROM DUAL UNION ALL
SELECT '002' AS KEY, 'Science' AS KEY_NAME, 1 AS SECTION FROM DUAL UNION ALL
SELECT '003' AS KEY, 'English' AS KEY_NAME, 2 AS SECTION FROM DUAL UNION ALL
SELECT '004' AS KEY, 'Japanese' AS KEY_NAME, 3 AS SECTION FROM DUAL UNION ALL
SELECT '005' AS KEY, 'Chinese' AS KEY_NAME, 3 AS SECTION FROM DUAL UNION ALL
SELECT '006' AS KEY, 'Media' AS KEY_NAME, 4 AS SECTION FROM DUAL UNION ALL
SELECT '007' AS KEY, 'Free' AS KEY_NAME, 5 AS SECTION FROM DUAL UNION ALL
SELECT '008' AS KEY, 'Manage' AS KEY_NAME, 5 AS SECTION FROM DUAL
),
REL AS(
SELECT '001' AS KEY, '003' AS NEXT_KEY FROM DUAL UNION ALL
SELECT '002' AS KEY, '003' AS NEXT_KEY FROM DUAL UNION ALL
SELECT '003' AS KEY, '004' AS NEXT_KEY FROM DUAL UNION ALL
SELECT '003' AS KEY, '005' AS NEXT_KEY FROM DUAL UNION ALL
SELECT '004' AS KEY, '006' AS NEXT_KEY FROM DUAL UNION ALL
SELECT '005' AS KEY, '006' AS NEXT_KEY FROM DUAL UNION ALL
SELECT '006' AS KEY, '007' AS NEXT_KEY FROM DUAL UNION ALL
SELECT '006' AS KEY, '008' AS NEXT_KEY FROM DUAL
)
SELECT RE.*, BA.SECTION, (SELECT SECTION FROM BASE WHERE KEY = RE.NEXT_KEY) AS AFTER_SECTION
FROM BASE BA,
REL RE
WHERE BA.KEY = RE.KEY
/* result */
| SECTION1 | SECTION2 | SECTION3 | SECTION4 | SECTION5 |
| Math | English | Japanese | Media | Free |
| Science | English | Chinese | Media | Manage |
계층구조의 중간과정이 겹쳐지고 있습니다. (English 와 Media 부분)
이렇게 되면 경로가 더 세분화 되게 됩니다.
테이블 원본이 이상한건가요? 아니면 결과를 잘못 예측한 건가요?
아니면 결과 도출을 위한 다른 정보가 더 있는 건가요?
Math-English-Japanese-Media-Free
Math-English-Japanese-Media-Manage
Math-English-Chinese-Media-Free
Math-English-Chinese-Media-Manage
Science-English-Japanese-Media-Free
Science-English-Japanese-Media-Manage
Science-English-Chinese-Media-Free
Science-English-Chinese-Media-Manage
아.. 죄송합니다. 제가 이해를 못하고 있었어요..
마농님이 적어주신 결과물이 나와야 합니다..!
WITH base AS
(
SELECT '001' key, 'Math' key_name, 1 section FROM dual
UNION ALL SELECT '002', 'Science' , 1 FROM dual
UNION ALL SELECT '003', 'English' , 2 FROM dual
UNION ALL SELECT '004', 'Japanese', 3 FROM dual
UNION ALL SELECT '005', 'Chinese' , 3 FROM dual
UNION ALL SELECT '006', 'Media' , 4 FROM dual
UNION ALL SELECT '007', 'Free' , 5 FROM dual
UNION ALL SELECT '008', 'Manage' , 5 FROM dual
)
, rel AS
(
SELECT '001' key, '003' next_key FROM dual
UNION ALL SELECT '002', '003' FROM dual
UNION ALL SELECT '003', '004' FROM dual
UNION ALL SELECT '003', '005' FROM dual
UNION ALL SELECT '004', '006' FROM dual
UNION ALL SELECT '005', '006' FROM dual
UNION ALL SELECT '006', '007' FROM dual
UNION ALL SELECT '006', '008' FROM dual
)
SELECT REGEXP_SUBSTR(x, '[^-]+', 1, 1) section1
, REGEXP_SUBSTR(x, '[^-]+', 1, 2) section2
, REGEXP_SUBSTR(x, '[^-]+', 1, 3) section3
, REGEXP_SUBSTR(x, '[^-]+', 1, 4) section4
, REGEXP_SUBSTR(x, '[^-]+', 1, 5) section5
FROM (SELECT CONNECT_BY_ROOT(b.key_name)
|| SYS_CONNECT_BY_PATH(c.key_name, '-') x
FROM rel a
, base b
, base c
WHERE a.key = b.key
AND a.next_key = c.key
AND CONNECT_BY_ISLEAF = 1
START WITH b.section = 1
CONNECT BY PRIOR a.next_key = a.key
)
;
마농님 또 질문드려서 죄송합니다.. ㅠㅠ 혹시 아래처럼 REL테이블에 TYPE이 하나 추가되어서
TYPE별로 계층쿼리가 돌 수 있을까요? 결과는 아래.. 있습니다 ㅠ
WITH BASE AS(
SELECT '001' AS KEY, 'Math' AS KEY_NAME, 1 AS SECTION FROM DUAL UNION ALL
SELECT '002' AS KEY, 'Science' AS KEY_NAME, 1 AS SECTION FROM DUAL UNION ALL
SELECT '003' AS KEY, 'English' AS KEY_NAME, 2 AS SECTION FROM DUAL UNION ALL
SELECT '004' AS KEY, 'Japanese' AS KEY_NAME, 3 AS SECTION FROM DUAL UNION ALL
SELECT '005' AS KEY, 'Chinese' AS KEY_NAME, 3 AS SECTION FROM DUAL UNION ALL
SELECT '006' AS KEY, 'Media' AS KEY_NAME, 4 AS SECTION FROM DUAL UNION ALL
SELECT '007' AS KEY, 'Free' AS KEY_NAME, 5 AS SECTION FROM DUAL UNION ALL
SELECT '008' AS KEY, 'Manage' AS KEY_NAME, 5 AS SECTION FROM DUAL
),
REL AS(
SELECT '001' AS KEY, '003' AS NEXT_KEY, 'A' TYPE FROM DUAL UNION ALL
SELECT '002' AS KEY, '003' AS NEXT_KEY, 'A' TYPE FROM DUAL UNION ALL
SELECT '003' AS KEY, '004' AS NEXT_KEY, 'A' TYPE FROM DUAL UNION ALL
SELECT '003' AS KEY, '005' AS NEXT_KEY, 'A' TYPE FROM DUAL UNION ALL
SELECT '004' AS KEY, '006' AS NEXT_KEY, 'A' TYPE FROM DUAL UNION ALL
SELECT '005' AS KEY, '006' AS NEXT_KEY, 'A' TYPE FROM DUAL UNION ALL
SELECT '006' AS KEY, '007' AS NEXT_KEY, 'A' TYPE FROM DUAL UNION ALL
SELECT '006' AS KEY, '008' AS NEXT_KEY, 'A' TYPE FROM DUAL UNION ALL
SELECT '001' AS KEY, '003' AS NEXT_KEY, 'B' TYPE FROM DUAL UNION ALL
SELECT '003' AS KEY, '004' AS NEXT_KEY, 'B' TYPE FROM DUAL UNION ALL
SELECT '004' AS KEY, '006' AS NEXT_KEY, 'B' TYPE FROM DUAL UNION ALL
SELECT '006' AS KEY, '008' AS NEXT_KEY, 'B' TYPE FROM DUAL
)
SELECT REGEXP_SUBSTR(x, '[^-]+', 1, 1) section1
, REGEXP_SUBSTR(x, '[^-]+', 1, 2) section2
, REGEXP_SUBSTR(x, '[^-]+', 1, 3) section3
, REGEXP_SUBSTR(x, '[^-]+', 1, 4) section4
, REGEXP_SUBSTR(x, '[^-]+', 1, 5) section5
FROM (SELECT CONNECT_BY_ROOT(b.key_name)
|| SYS_CONNECT_BY_PATH(c.key_name, '-') x
FROM rel a
, base b
, base c
WHERE a.key = b.key
AND a.next_key = c.key
AND CONNECT_BY_ISLEAF = 1
START WITH b.section = 1
CONNECT BY PRIOR a.next_key = a.key
)
;
A Math-English-Japanese-Media-Free
A Math-English-Japanese-Media-Manage
A Math-English-Chinese-Media-Free
A Math-English-Chinese-Media-Manage
A Science-English-Japanese-Media-Free
A Science-English-Japanese-Media-Manage
A Science-English-Chinese-Media-Free
A Science-English-Chinese-Media-Manage
B Math-English-Japanese-Media-Manage
감사합니다. 계층쿼리는 항상 어렵네요. 다 이해하지는 못했지만 더 공부하겠습니다. 마농님 존경합니다!
-- 계층 조건 추가 --
CONNECT BY PRIOR a.next_key = a.key
AND PRIOR a.type = a.type -- 추가
) 버튼을 클릭하여 작성 하시면 됩니다.