connect by 에서 각 레벨로 값 얻기 1 5 1,106

by 농부지기 [Oracle 기초] [2016.12.21 17:00:36]



WITH A AS
  (SELECT '1' AS COL, '물'   AS NAME, ''  AS UP_COL FROM DUAL UNION ALL
   SELECT '2' AS COL, '뿌리'  AS NAME, '1' AS UP_COL FROM DUAL UNION ALL
   SELECT '3' AS COL, '나무'  AS NAME, '2' AS UP_COL FROM DUAL UNION ALL
   SELECT '4' AS COL, '나뭇잎' AS NAME, '3' AS UP_COL FROM DUAL UNION ALL
   SELECT 'A' AS COL, '나무'   AS NAME, ''  AS UP_COL FROM DUAL UNION ALL
   SELECT 'B' AS COL, '종이'  AS NAME, 'A' AS UP_COL FROM DUAL UNION ALL
   SELECT 'C' AS COL, '공책'  AS NAME, 'B' AS UP_COL FROM DUAL UNION ALL
   SELECT 'D' AS COL, '서점' AS NAME, 'C' AS UP_COL FROM DUAL
   )
SELECT COL, NAME 
     , CONNECT_BY_ROOT COL AS COL_1
     , CONNECT_BY_ROOT NAME AS NAME_1
     --?? AS COL_2
     --?? AS NAME_2
     --?? AS COL_3
     --?? AS NAME_3
     , DECODE(CONNECT_BY_ISLEAF, 1, COL)   AS COL_4
     , DECODE(CONNECT_BY_ISLEAF, 1, NAME)  AS NAME_4
  FROM A
 START WITH UP_COL IS NULL
 CONNECT BY PRIOR COL = UP_COL
;

 

원하는 결과
 - 각 레코드별로 레벨별 값을 가지고 있고 싶은데요.
   CONNECT_BY_ROOT, CONNECT_BY_ISLEAF를 이용해서  첫번째와 마지막 LEVEL의 값은 구했는데요.
   두번째, 세번째 레벨에 대한 값을 어떻게 얻을 수 있을까요?
-------------------------------------------------------------------------
COL1    NAME     COL_1    NAME_1    COL_2   NAME_2 ........3,...4
-------------------------------------------------------------------------
   1        물          1           물        
   2        뿌리       1           물             2       뿌리
   3        나무       1           물             2       뿌리
   4        나뭇잎    1           물             2       뿌리
   A        나무      A           나무
   B        종이      A           나무           B       종이
   C        공책      A           나무           B       종이
   D        서점      A           나무           B       종이

 

 

by 우리집아찌 [2016.12.21 17:23:22]

sys_connect_by_path 찾아보세요

전 잘라서 씀


by 농부지기 [2016.12.21 17:35:05]

sys_connect_by_path 으로 SUBSTR()함수를 이용해서 찾을 수 있는데요.

전 혹시 CONNECT_BY_ROOT, CONNECT_BY_ISLEAF 같은 기능이 있는지 궁금해서요.


by jkson [2016.12.21 17:39:48]

CONNECT_BY_ROOT, CONNECT_BY_ISLEAF 예약어처럼 중간 과정에 접근하는 예약어가 있는지는 모르겠네요. 저는 못 봤습니다만..


by jkson [2016.12.21 17:38:22]
WITH A AS
  (SELECT '1' AS COL, '물'   AS NAME, ''  AS UP_COL FROM DUAL UNION ALL
   SELECT '2' AS COL, '뿌리'  AS NAME, '1' AS UP_COL FROM DUAL UNION ALL
   SELECT '3' AS COL, '나무'  AS NAME, '2' AS UP_COL FROM DUAL UNION ALL
   SELECT '4' AS COL, '나뭇잎' AS NAME, '3' AS UP_COL FROM DUAL UNION ALL
   SELECT 'A' AS COL, '나무'   AS NAME, ''  AS UP_COL FROM DUAL UNION ALL
   SELECT 'B' AS COL, '종이'  AS NAME, 'A' AS UP_COL FROM DUAL UNION ALL
   SELECT 'C' AS COL, '공책'  AS NAME, 'B' AS UP_COL FROM DUAL UNION ALL
   SELECT 'D' AS COL, '서점' AS NAME, 'C' AS UP_COL FROM DUAL
   )
SELECT COL, NAME
     , CONNECT_BY_ROOT COL AS COL_1
     , CONNECT_BY_ROOT NAME AS NAME_1
     , REGEXP_SUBSTR(SYS_CONNECT_BY_PATH (COL,'^'),'[^^]+',1,2) COL_2
     , REGEXP_SUBSTR(SYS_CONNECT_BY_PATH (NAME,'^'),'[^^]+',1,2) NAME_2
     , REGEXP_SUBSTR(SYS_CONNECT_BY_PATH (COL,'^'),'[^^]+',1,3) COL_3
     , REGEXP_SUBSTR(SYS_CONNECT_BY_PATH (NAME,'^'),'[^^]+',1,3) NAME_3
     , REGEXP_SUBSTR(SYS_CONNECT_BY_PATH (COL,'^'),'[^^]+',1,4) COL_4
     , REGEXP_SUBSTR(SYS_CONNECT_BY_PATH (NAME,'^'),'[^^]+',1,4) NAME_4
  FROM A
 START WITH UP_COL IS NULL
 CONNECT BY PRIOR COL = UP_COL


 


by 농부지기 [2016.12.22 09:39:01]

예약어는 없나보네요.

정규식으로 하니 깔끔하게 되는군요. 훌륭~~~

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