[SQL] 재귀 CTE(Command Table Expression)를 이용한 계층적 구조 탐색

SQL에서의 재귀(WITH RECURSIVE)
주로 계층 구조, 예를 들어 조직도, 카테고리 트리, 댓글 트리 등을 탐색할 때 사용된다.

 

 

✅ 예시 테이블: employees

employee_id	name	manager_id
1	CEO	NULL
2	ManagerA	1
3	ManagerB	1
4	StaffA1	2
5	StaffA2	2
6	StaffB1	3

 

✅ 목표

• CEO부터 시작해서, 각 직원이 누구 밑에 있는지를 계층적으로 출력
• 그리고 각 직원의 레벨(깊이) 도 함께 보여줌

 

WITH RECURSIVE ORG_CHART AS (
	-- 1단계: 루트 직원(CEO)을 가져온다.
    SELECT
    	EMPLOYEE_ID,
        NAME,
        MANAGER_ID,
        1 AS LEVEL
    FROM EMPLOYEES
    WHERE MANAGER_ID IS NULL
    
    UNION ALL
    
    -- 2단계 이상: 자기 자신을 재귀적으로 연결한다.
    SELECT
    	E.EMPLOYEE_ID,
        E.NAME,
        E.MANAGER_ID,
        OC.LEVEL + 1 AS LEVEL
    FROM EMPLOYEES E
    JOIN ORG_CHART OC ON E.MANAGER_ID = OC.EMPLOYEE_ID
)

SELECT * FROM ORG_CHART
ORDER BY LEVEL, MANAGER_ID, EMPLOYEE_ID;

 

✅ 결과

employee_id	name	manager_id	level
1	CEO	NULL	1
2	ManagerA	1	2
3	ManagerB	1	2
4	StaffA1	2	3
5	StaffA2	2	3
6	StaffB1	3	3

 

 

참고할만한 문제

https://moon-code.tistory.com/entry/SQL-%EC%9E%85%EC%96%91-%EC%8B%9C%EA%B0%81-%EA%B5%AC%ED%95%98%EA%B8%B0-2

[SQL] 입양 시각 구하기 (2)