[DB/SQL] 계층형 쿼리 정리 ( START WITH, CONNECT BY PRIOR, ORDER SIBLINGS BY)

 

 

 

최근에 회사에서 종속된 관계를 다루는 쿼리를 짜면서..

계층형 쿼리를 짜게 되었는데요~

알아두면 좋을거같아서 정리해보겠습니다!

 

 

문법정리

- 계층형 쿼리란? 부모, 자식 간의 수직관계를 트리 구조 형태로 보여주는 쿼리

 

 

 

예제

 

[테이블 생성]

위에 문법을 적용할 테이블을 준비했습니다. 간단하게 테스트하시는 분은 따라해보시면 좋습니다.

CREATE TABLE DPT (
 HI_DPT_CD  VARCHAR2(20), --상위부서
 LO_DPT_CD  VARCHAR2(20), --하위부서
 SEQ    number(3)	--부서이력
 STDT VARCHAR2(8)	--시작일시
);

어디 조직이든 한번씩은 있는 부서 테이블 입니다.

 

 

[테스트 데이터 입력]

/*
ITHQ : IT본부
ITDV : IT개발팀
ITDVS : IT개발셀
*/
INSERT INTO DPT (HI_DPT_CD, LO_DPT_CD, SEQ, STDT) VALUES ('ITHQ01','ITDV01', 1, '20240101')
INSERT INTO DPT (HI_DPT_CD, LO_DPT_CD, SEQ, STDT ) VALUES ('ITHQ01','ITDV02', 1, '20240201')
INSERT INTO DPT (HI_DPT_CD, LO_DPT_CD, SEQ, STDT ) VALUES ('ITHQ01','ITDV03', 1, '20240301')

INSERT INTO DPT (HI_DPT_CD, LO_DPT_CD, SEQ, STDT ) VALUES ('ITDV01','ITDVS11', 1, '20240102')
INSERT INTO DPT (HI_DPT_CD, LO_DPT_CD, SEQ, STDT ) VALUES ('ITDV01','ITDVS12', 1, '20240103')
INSERT INTO DPT (HI_DPT_CD, LO_DPT_CD, SEQ, STDT ) VALUES ('ITDV01','ITDVS13', 1, '20240104')

INSERT INTO DPT (HI_DPT_CD, LO_DPT_CD, SEQ, STDT ) VALUES ('ITDV02','ITDVS21', 1, '20240202')
INSERT INTO DPT (HI_DPT_CD, LO_DPT_CD, SEQ, STDT ) VALUES ('ITDV02','ITDVS22', 1, '20240203')
INSERT INTO DPT (HI_DPT_CD, LO_DPT_CD, SEQ, STDT ) VALUES ('ITDV02','ITDVS23', 1, '20240204')

INSERT INTO DPT (HI_DPT_CD, LO_DPT_CD, SEQ, STDT ) VALUES ('ITDV03','ITDVS31', 1, '20240302')
INSERT INTO DPT (HI_DPT_CD, LO_DPT_CD, SEQ, STDT ) VALUES ('ITDV03','ITDVS32', 1, '20240303')
INSERT INTO DPT (HI_DPT_CD, LO_DPT_CD, SEQ, STDT ) VALUES ('ITDV03','ITDVS33', 1, '20240304')

 

IT회사에 각 부서정보를 임시로 넣어보았습니다.

 

 

[부모에서 자식으로 순방향 계층쿼리]

SELECT 
     DPT.HI_DPT_CD
    ,DPT.LO_DPT_CD
    ,STDT
    ,LEVEL
    FROM DPT
    START WITH HI_DPT_CD = 'ITHQ01'
    CONNECT BY PRIOR LO_DPT_CD = HI_DPT_CD

 

실행 결과를 보면 최상위 부모행('ITHQ01')에서 자식이 없을 때까지 탐색하는 것을 확인할 수 있습니다.

 

 

 

[부모에서 자식으로 순방향 계층쿼리+조건절]

SELECT 
     DPT.HI_DPT_CD
    ,DPT.LO_DPT_CD
    ,STDT
    ,LEVEL
    FROM DPT
    START WITH HI_DPT_CD = 'ITHQ01'
    CONNECT BY PRIOR LO_DPT_CD = HI_DPT_CD
                 AND HI_DPT_CD = 'ITHQ01'

 

 

조건절도 넣을 수 있는데요, 조건으로 상위('ITHQ01')를 넣으면 조건에 맞는 것만 조회가능합니다.

 

 

[부모에서 자식으로 순방향 계층쿼리+정렬]

SELECT 
     DPT.HI_DPT_CD
    ,DPT.LO_DPT_CD
    ,STDT
    ,LEVEL
    FROM DPT
    START WITH HI_DPT_CD = 'ITHQ01'
    CONNECT BY PRIOR LO_DPT_CD = HI_DPT_CD
    ORDER SIBLINGS BY STDT DESC

결과를 확인해보면, ORDER SIBLINGS BY로 STDT(시작일시)에 따른 정렬된 결과를 볼수 있습니다.

 

 

[자식에서 부모으로 역방향 계층쿼리]

SELECT 
     DPT.HI_DPT_CD
    ,DPT.LO_DPT_CD
    , STDT
    , LEVEL
    FROM DPT
    START WITH LO_DPT_CD = 'ITDVS13'
    CONNECT BY  LO_DPT_CD = PRIOR HI_DPT_CD

마지막 부모의 계층까지 탐색후 종료합니다.

 

 

회사에서 자주 사용하는 계층형 쿼리를 간단히 정리해보았습니다.

감사합니다.