계층구조 SQL

CREATE TABLE WAF_CATEGORY (
CTGY_ID     NUMBER        NOT NULL,
SITE_CODE   VARCHAR2 (20),
CTGY_CODE   VARCHAR2 (20),
CTGY_TYPE   VARCHAR2 (10),
CTGY_NAME   VARCHAR2 (50),
CTGY_DEPTH  VARCHAR2 (10),
CTGY_PATH   VARCHAR2 (200),
EXE_ID      VARCHAR2 (20),
PARENT_ID   NUMBER,
CRE_BY      NUMBER,
CRE_DATE    DATE,
UPD_BY      NUMBER,
UPD_DATE    DATE,
PRIMARY KEY ( CTGY_ID ) ) ;

 

SQL> select ctgy_id, parent_id, ctgy_depth, ctgy_code, ctgy_name
2* from waf_category order by ctgy_id;
CTGY_ID PARENT_ID CTGY_DEPTH CTGY_CODE            CTGY_NAME
--------- --------- ---------- -------------------- ---------------------------
      53           0                               테스트현장
      54        53 1          1                    101동
      55        54 2          1.1                  건축공사
      56        55 3          1.1.1                가설공사
      57        55 3          1.1.2                토공및지정공사
      58        55 3          1.1.3                철근CON'C공사
      59        55 3          1.1.11               지붕.홈통공사
      60        55 3          1.1.12               창호공사

8 개의 행이 선택되었습니다.

SQL>

 

SQL> select ctgy_id, parent_id, ctgy_depth, ctgy_code,
           lpad(' ', 4*(level-1)) ||  ctgy_name as name
      from waf_category
     where site_code = '6250022301'
     start with ctgy_id=53
   connect by prior ctgy_id=parent_id

 

  CTGY_ID PARENT_ID CTGY_DEPTH CTGY_CODE   NAME
--------- --------- ---------- ----------- ---------------
      53           0                      테스트현장
      54        53 1          1             101동
      55        54 2          1.1             건축공사
      56        55 3          1.1.1             가설공사
      57        55 3          1.1.2             토공및지정공사
      58        55 3          1.1.3             철근CON'C공사
      59        55 3          1.1.11            지붕.홈통공사
      60        55 3          1.1.12            창호공사
8 개의 행이 선택되었습니다.