꿈꾸는 개발자, DBA 커뮤니티 구루비

계층에서 특정 부분들만 조회하기 조언 바랍니다.

by 별님 [SQL Query] 계층구조 [2013.11.20 04:33:27]

 
WITH TBL AS (
SELECT '1' C_ID, NULL R_ID, '1장' R_TI FROM DUAL UNION ALL
SELECT '11' C_ID, '1' R_ID, '1조' R_TI FROM DUAL UNION ALL
SELECT '12' C_ID, '1' R_ID, '2조' R_TI FROM DUAL UNION ALL
SELECT '121' C_ID, '12' R_ID, '1항' R_TI FROM DUAL UNION ALL
SELECT '122' C_ID, '12' R_ID, '2항' R_TI FROM DUAL UNION ALL
SELECT '123' C_ID, '12' R_ID, '3항' R_TI FROM DUAL UNION ALL
SELECT '2' C_ID, NULL R_ID, '2장' R_TI FROM DUAL UNION ALL
SELECT '21' C_ID, '2' R_ID, '3조' R_TI FROM DUAL UNION ALL
SELECT '22' C_ID, '2' R_ID, '4조' R_TI FROM DUAL UNION ALL
SELECT '221' C_ID, '22' R_ID, '1항' R_TI FROM DUAL UNION ALL
SELECT '222' C_ID, '22' R_ID, '2항' R_TI FROM DUAL UNION ALL
SELECT '23' C_ID, '2' R_ID, '1절' R_TI FROM DUAL UNION ALL
SELECT '231' C_ID, '23' R_ID, '5조' R_TI FROM DUAL UNION ALL
SELECT '232' C_ID, '23' R_ID, '6조' R_TI FROM DUAL UNION ALL
SELECT '2321' C_ID, '232' R_ID, '1항' R_TI FROM DUAL UNION ALL
SELECT '2322' C_ID, '232' R_ID, '2항' R_TI FROM DUAL
)

>> 위의 데이터에서
   조회 조건은 LEAF가 없는 C_ID 입니다.
   LEAF가 없는 1조 3조 5조는 자기 자신만 조회되고
   LFAF가 있는 2조 4조 6조는 자신을 포함해 하위까지 모두 뽑고 싶은데 잘 안되서 질문드립니다...

오늘까지 해야하는데 큰일이네요..ㅜㅜ 도와주세요

ORACLE 버전은 10g 입니다.

   EX) C_ID IN (123,231,11,2321) 일경우
C_ID   R_ID    R_TI    CHOICE
----------------------------------------------
   11 1    1조 Y
   12 1     2조
121 12    1항
122 12     2항
123 12   3항        Y
231   23    5조     Y
232    23     6조
2321   232 1항    Y
2322   232    2항

by 우리집아찌 [2013.11.20 10:03:25]

 
-- 무식하게 하기..
WITH TBL AS ( 
SELECT '1' C_ID, NULL R_ID, '1장' R_TI FROM DUAL UNION ALL
SELECT '11' C_ID, '1' R_ID, '1조' R_TI FROM DUAL UNION ALL
SELECT '12' C_ID, '1' R_ID, '2조' R_TI FROM DUAL UNION ALL
SELECT '121' C_ID, '12' R_ID, '1항' R_TI FROM DUAL UNION ALL
SELECT '122' C_ID, '12' R_ID, '2항' R_TI FROM DUAL UNION ALL
SELECT '123' C_ID, '12' R_ID, '3항' R_TI FROM DUAL UNION ALL
SELECT '2' C_ID, NULL R_ID, '2장' R_TI FROM DUAL UNION ALL
SELECT '21' C_ID, '2' R_ID, '3조' R_TI FROM DUAL UNION ALL
SELECT '22' C_ID, '2' R_ID, '4조' R_TI FROM DUAL UNION ALL
SELECT '221' C_ID, '22' R_ID, '1항' R_TI FROM DUAL UNION ALL
SELECT '222' C_ID, '22' R_ID, '2항' R_TI FROM DUAL UNION ALL
SELECT '23' C_ID, '2' R_ID, '1절' R_TI FROM DUAL UNION ALL
SELECT '231' C_ID, '23' R_ID, '5조' R_TI FROM DUAL UNION ALL
SELECT '232' C_ID, '23' R_ID, '6조' R_TI FROM DUAL UNION ALL
SELECT '2321' C_ID, '232' R_ID, '1항' R_TI FROM DUAL UNION ALL
SELECT '2322' C_ID, '232' R_ID, '2항' R_TI FROM DUAL 
) 


SELECT * FROM TBL
WHERE R_ID IN (SELECT R_ID FROM TBL WHERE C_ID IN (123,231,11,2321) )
ORDER BY C_ID

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변