by 짱구 [SQL Query] oracle connect connectby [2020.03.26 12:59:42]
무한인 계층구조 데이터에서
구하고자 하는 값은 CD라는 Column에 '1'이 포함되어 있으면 제일 상위를 찾아 계층을 표현하고 그 계층에서 '1'이 포함되어 있지 않는
자식 노드는 제거 하고 싶습니다. (Tree에서 검색을 하면 어떤 LEVEL에서든 검색된 계층구조를 찾아 Tree를 구현하기 위함입니다.)
WIHT AS ( SELECT '9' AS CD, '' AS PCD FROM DUAL UNION ALL SELECT '11' AS CD, '9' AS PCD FROM DUAL UNION ALL SELECT '22' AS CD, '9' AS PCD FROM DUAL UNION ALL SELECT '33' AS CD, '9' AS PCD FROM DUAL UNION ALL SELECT '111' AS CD, '11' AS PCD FROM DUAL UNION ALL SELECT '222' AS CD, '11' AS PCD FROM DUAL UNION ALL SELECT '112' AS CD, '11' AS PCD FROM DUAL UNION ALL SELECT '333' AS CD, '33' AS PCD FROM DUAL ) SELECT CD , CONNECT_BY_ROOT CD , CONNECT_BY_ISLEAF , SYS_CONNECT_BY_PATH(CD, '/') , LEVEL FROM T WHERE 'Y' = CASE WHEN CONNECT_BY_ISLEAF = 1 AND CD LIKE '%1%' THEN 'Y' WHEN CONNECT_BY_ISLEAF = 1 THEN 'N' ELSE 'Y' END START WITH CD IN (SELECT DISTINCT CD FROM T WHERE CONNECT_BY_ISLEAF = 1 START WITH CD LIKE '%1%' CONNECT BY PRIOR PCD = CD) CONNECT BY PRIOR CD = PCD ORDER SIBLINGS BY PCD ASC, CD ASC
제가 위와같이 해보았습니다. 그랬더니 아래와 같이 값이 나옵니다.
1 9 9 0 /9 1 2 11 9 0 /9/11 2 3 111 9 1 /9/11/111 3 4 112 9 1 /9/11/112 3 5 33 9 0 /9/33 2
여기서 5에 해당하는 33은 나오지 말아야 하는데 말이죠 ㅡ.ㅡ;;
즉,
1 9 9 0 /9 1 2 11 9 0 /9/11 2 3 111 9 1 /9/11/111 3 4 112 9 1 /9/11/112 3
이렇게만 나오게 할 수 있는 Query 여쭈어 봅니다.
감사합니다.
WITH t AS ( SELECT '9' cd, '' pcd FROM dual UNION ALL SELECT '11' , '9' FROM dual UNION ALL SELECT '22' , '9' FROM dual UNION ALL SELECT '33' , '9' FROM dual UNION ALL SELECT '111', '11' FROM dual UNION ALL SELECT '222', '11' FROM dual UNION ALL SELECT '112', '11' FROM dual UNION ALL SELECT '333', '33' FROM dual ) SELECT cd , CONNECT_BY_ROOT cd root_cd , CONNECT_BY_ISLEAF is_leaf , SYS_CONNECT_BY_PATH(cd, '/') by_path , LEVEL lv FROM (SELECT DISTINCT cd, pcd , CONNECT_BY_ISLEAF x FROM t START WITH INSTR(cd, '1') > 0 CONNECT BY PRIOR pcd = cd ) START WITH x = 1 CONNECT BY PRIOR cd = pcd ORDER SIBLINGS BY cd ;