데이터베이스 질문과 답변
WITH T AS(
SELECT '0' AS PARENT_NUM,'111' AS NUM ,'A' AS SUBJECT FROM DUAL
UNION ALL
SELECT '111' AS PARENT_NUM,'222' AS NUM ,'B' AS SUBJECT FROM DUAL
UNION ALL
SELECT '222' AS PARENT_NUM,'333' AS NUM ,'C' AS SUBJECT FROM DUAL
UNION ALL
SELECT '0' AS PARENT_NUM,'444' AS NUM ,'D' AS SUBJECT FROM DUAL
UNION ALL
SELECT '444' AS PARENT_NUM,'555' AS NUM ,'E' AS SUBJECT FROM DUAL
)
SELECT LPAD(' ', 4*(LEVEL-1)) || SUBJECT AS SUBJECT FROM T
START WITH PARENT_NUM = 0
CONNECT BY PRIOR NUM = PARENT_NUM;
SUBJECT
--------------
A
B
C
D
E
자식 조건이 SUBJECT='E' 일 경우
SUBJECT
--------------
A
B
C
이런식으로 추출하고 싶습니다.
도움 부탁 드립니다.
by 동동동
[2021.09.10 08:20:19]
질문이 E가 속한 모든 Row(부모,자식)를 제외하고 싶다는 건지요?
by 겨울눈
[2021.09.13 10:19:12]
답변 확인이 늦었습니다~
네 맞습니다. 아랫분이 답변 주셨네요
by 샤랄라
[2021.09.10 16:47:39]
WITH T AS(
SELECT '0' AS PARENT_NUM,'111' AS NUM ,'A' AS SUBJECT FROM DUAL
UNION ALL
SELECT '111' AS PARENT_NUM,'222' AS NUM ,'B' AS SUBJECT FROM DUAL
UNION ALL
SELECT '222' AS PARENT_NUM,'333' AS NUM ,'C' AS SUBJECT FROM DUAL
UNION ALL
SELECT '0' AS PARENT_NUM,'444' AS NUM ,'D' AS SUBJECT FROM DUAL
UNION ALL
SELECT '444' AS PARENT_NUM,'555' AS NUM ,'E' AS SUBJECT FROM DUAL
)
SELECT SUBJECT
--, SUBJECT_PATH
--, P_SUBJECT
FROM (SELECT LPAD(' ', 4*(LEVEL-1)) || SUBJECT AS SUBJECT
, MAX(SYS_CONNECT_BY_PATH(SUBJECT, ',')) OVER(PARTITION BY CONNECT_BY_ROOT SUBJECT) AS SUBJECT_PATH
, CONNECT_BY_ROOT SUBJECT AS P_SUBJECT
FROM T
START WITH PARENT_NUM = 0
CONNECT BY PRIOR NUM = PARENT_NUM
)
WHERE SUBJECT_PATH NOT LIKE '%,'||'E'||'%'
;
by 겨울눈
[2021.09.13 10:19:31]
정말 감사합니다~
새로운 지식 알아갑니다 ^^
) 버튼을 클릭하여 작성 하시면 됩니다.