계층형 쿼리 질문 드립니다. 0 4 198

by 겨울눈 [SQL Query] [2021.09.09 11:41:16]


 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]

정말 감사합니다~

새로운 지식 알아갑니다 ^^

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입