|
문득 이런 기능도 있지 않을까 하는 생각에 질문드립니다.
START WITH... CONNECT BY 로 연결시킨 구문에서
SYS_CONNECT_BY_PATH를 통해서 값을 가져오는건 많이 해봤는데...
혹시 연결된 값중 특정 레벨의 값을 뽑는다거나... 특정 조건의 값을 뽑는 것이 가능한가요??
당췌 말로 써서는 저도 이해 못할꺼 같아서...
WITH TABLE_A AS (
SELECT 1 AS NUM, 'VALUE1' AS VALUE, '0001' AS CODE FROM DUAL UNION ALL
SELECT 2 AS NUM, 'VALUE2' AS VALUE, '0001' AS CODE FROM DUAL UNION ALL
SELECT 3 AS NUM, 'VALUE3' AS VALUE, '0001' AS CODE FROM DUAL UNION ALL
SELECT 4 AS NUM, 'VALUE4' AS VALUE, '0001' AS CODE FROM DUAL UNION ALL
SELECT 5 AS NUM, 'VALUE5' AS VALUE, '0002' AS CODE FROM DUAL UNION ALL
SELECT 6 AS NUM, 'VALUE6' AS VALUE, '0001' AS CODE FROM DUAL UNION ALL
SELECT 7 AS NUM, 'VALUE7' AS VALUE, '0001' AS CODE FROM DUAL UNION ALL
SELECT 8 AS NUM, 'VALUE8' AS VALUE, '0001' AS CODE FROM DUAL UNION ALL
SELECT 9 AS NUM, 'VALUE9' AS VALUE, '0001' AS CODE FROM DUAL UNION ALL
SELECT 10 AS NUM, 'VALUE10' AS VALUE, '0001' AS CODE FROM DUAL
)
SELECT SYS_CONNECT_BY_PATH(NUM, ' > ') AS PATH_NUM
, SYS_CONNECT_BY_PATH(VALUE, ' > ') AS PATH_VALUE
, AA.*
FROM TABLE_A AA
START WITH NUM = 1 -- 시작조건은 언제나 1일때 입니다.
CONNECT BY NUM = PRIOR NUM + 1
ORDER SIBLINGS BY NUM
조회 결과는 다음과 같습니다.
NUM VALUE CODE PATH_NUM LEVEL1 LEVEL2 CODE=0002
1 VALUE1 0001 > 1 1
2 VALUE2 0001 > 1 > 2 1 2
3 VALUE3 0001 > 1 > 2 > 3 1 2
4 VALUE4 0001 > 1 > 2 > 3 > 4 1 2
5 VALUE5 0002 > 1 > 2 > 3 > 4 > 5 1 2 5
6 VALUE6 0001 > 1 > 2 > 3 > 4 > 5 > 6 1 2 5
7 VALUE7 0001 > 1 > 2 > 3 > 4 > 5 > 6 > 7 1 2 5
8 VALUE8 0001 > 1 > 2 > 3 > 4 > 5 > 6 > 7 > 8 1 2 5
9 VALUE9 0001 > 1 > 2 > 3 > 4 > 5 > 6 > 7 > 8 > 9 1 2 5
10 VALUE10 0001 > 1 > 2 > 3 > 4 > 5 > 6 > 7 > 8 > 9 > 10 1 2 5
검정색은 SYS_CONNECT_BY_PATH 등으로 뽑아낸 값인데...
빨간색 부분이 제가 정작 뽑아 내고 싶은 것입니다.
LEVEL1에 해당하는 값 LEVEL2에 해당하는 값, 그리고 특정 조건의 값 등을 뽑아내고 싶은데...
뭔가 쉬운 방법이 있는지 궁금하네요... |
