[SQL]계층형 질의문으로 서열을 주고 데이터 출력하기

1. 계층 순서로 출력하기

계층형 질의문(Hierachical Query)를 사용해 데이터를 선택하여 계층 순서로 결과를 출력한다.

계층형 질의문에서 쓰이는 용어

용어 설명

노드(node)	표시된 항목
레벨(level)	트리(tree) 구조에서 각각의 계층
루트(root)	트리(tree) 구조에서 최상위에 있는 노드
부모(parent)	트리(tree) 구조에서 상위에 있는 노드
자식(child)	트리(tree) 구조에서 하위에 있는 노드

 

계층형 질의문 형식 예시

SELECT  LEVEL, COL1, COL2, ...
FROM TABLE_NAME
START WITH ROOT_NODE
CONNECT BY PRIOR PARENT_NODE = CHILD_NODE

계층형 질의문의 키워드인 CONNECT BY와 START WITH절을 사용하면 pseudo column인 LEVEL(서열)을 출력할 수 있다.

START WITH절은 최상위 노드인 루트 노드의 데이터를 지정한다.

CONNECT BY절은 RPIOR을 가운데로 두고 왼쪽에는 부모노드가 되는 컬럼을, 오른쪽에는 자식노드가 되는 컬럼을 지정한다

 

EX ) 계층형 질의문을 이용하여 사원 이름, 월급, 직업을 출력하는데 사원들 간의 서열(LEVEL)을 같이 출력하라

EMP 테이블에는 사원번호컬럼인 EMPNO와 직속상사의 사원번호정보가 있는 MGR컬럼이 있다. 이를 이용하여 문제를 해결한다.

SELECT RPAD(' ', LEVEL*3) || ENAME AS EMPLOYEE, LEVEL, SAL, JOB
FROM EMP
START WITH ENAME = 'KING'
CONNECT BY PRIOR EMPNO = MGR;

 

2. 특정 분기 제거하고 출력하기

EX ) 위 예제 결과에서 BLAKE와 BLAKE의 직속 부하들은 출력되지 않도록 하라

만약 BLAKE만 제외하고 출력하기 위해서는 WHERE절에 ENAME != 'BLAKE' 조건을 주면 된다. 하지만 BLAKE와 그의 직속 부하들 또한 제외해야한다.

이 처럼 특정 부모 노드와 그에 따른 자식 노드노 제외할 경우엔 CONNECT BY절에 조건을 준다.

SELECT RPAD(' ',LEVEL * 3) || ENAME AS EMPLOYEE, LEVEL, SAL, JOB
FROM EMP
START WITH ENAME = 'KING'
CONNECT BY PRIOR EMPNO = MGR AND ENAME != 'BLAKE';

 

728x90

 

3. 정렬해서 출력하기

계층형 질의문의 결과를 유지하며 데이터를 정렬해서 출력하기 위해 ORDER BY절에 SIBLNGS를 사용한다.

 

EX ) 계층형 질의문을 이용하여 사원 이름, 월급, 직업을 서열과 같이 출력하는데, 서열 순서를 유지하면서 월급이 높은 사원부터 출력하라

정렬순서 : LEVEL > 월급

-- ORDER BY 절만 사용한 경우
SELECT RPAD(' ', LEVEL* 3) || ENAME AS EMPLOYEE, LEVEL, SAL, JOB
FROM EMP
START WITH ENAME = 'KING'
CONNECT BY PRIOR EMPNO = MGR
ORDER BY SAL DESC;

ORDER BY절만 사용하여 정렬할 경우 부모노드와 자식노드 관계에 상관 없이 서열 순서가 섞이고 월급이 높은 순서로 정렬됨을 알 수 있다.

 

-- ORDER SIBLINGS BY 을 사용한 경우
SELECT RPAD(' ', LEVEL* 3) || ENAME AS EMPLOYEE, LEVEL, SAL, JOB
FROM EMP
START WITH ENAME = 'KING'
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY SAL DESC;

ORDER BY 사이에 SIBLINGS를 사용하여 정렬하면 계층형 질의문의 서열 순서를 깨트리지 않으면서 월급이 높은 순서대로 출력할 수 있다.

 

4. 가로로 출력하기

서열 순서를 가로로 출력하기 위해 계층형 질의문에 SYS_CONNECT_BY_PATH 함수를 사용한다.

EX ) 계층형 질의문을 이용하여 사원 이름과 서열순서를 가로로 출력하라

SELECT ENAME, SYS_CONNECT_BY_PATH(ENAME, '/') AS PATH
FROM EMP
START WITH ENAME = 'KING'
CONNECT BY PRIOR EMPNO = MGR;

SYS_CONNECT_BY_PATH 함수에 두 번째 인자 값으로 ‘/’를 사용해서 이름과 이름 사이의 연결을 ‘/’로 출력한다.

좀 더 보기 좋게 LTRIM을 사용해 가로로 출력되는 이름 앞에 ‘/’를 제거하고 출력할 수도 있다.

SELECT ENAME, LTRIM(SYS_CONNECT_BY_PATH(ENAME, '/'), '/') AS PATH
FROM EMP
START WITH ENAME = 'KING'
CONNECT BY PRIOR EMPNO = MGR;