소스: sql-src/adv_04_hierarchy/01_recursive_cte.sql. 사원 11명이 mgr 로 4단계 조직을 이룹니다. 김대표(1) 아래 남본부(2)·이본부(3), 그 아래 팀장, 그 아래 팀원입니다.
WITH RECURSIVE t (id, name, mgr, lvl, path) AS (
SELECT id, name, mgr, 1, CAST('/' || name AS VARCHAR(200))
FROM emp
WHERE mgr IS NULL
UNION ALL
SELECT e.id, e.name, e.mgr, t.lvl + 1, CAST(t.path || '/' || e.name AS VARCHAR(200))
FROM emp e
JOIN t ON e.mgr = t.id
)
SELECT
lvl
, LPAD(name, CHAR_LENGTH(name) + (lvl - 1) * 2, ' ') AS tree_name
, path
FROM t
ORDER BY path;LVL | TREE_NAME | PATH
----+--------------+-----------------------------
1 | 김대표 | /김대표
2 | 남본부 | /김대표/남본부
3 | 박팀장 | /김대표/남본부/박팀장
4 | 강민호 | /김대표/남본부/박팀장/강민호
4 | 노지은 | /김대표/남본부/박팀장/노지은
3 | 서팀장 | /김대표/남본부/서팀장
4 | 문서준 | /김대표/남본부/서팀장/문서준
2 | 이본부 | /김대표/이본부
3 | 최팀장 | /김대표/이본부/최팀장
4 | 오하늘 | /김대표/이본부/최팀장/오하늘
4 | 임도윤 | /김대표/이본부/최팀장/임도윤
(11행)앵커는 mgr IS NULL 인 김대표 한 명이고, 재귀부가 한 단계씩 부하를 붙입니다. path 로 정렬하면 부모 바로 아래에 자식이 오는 트리 순서가 됩니다. LPAD 는 깊이에 비례해 앞에 공백을 채워 들여쓰기를 만듭니다.
path 컬럼은 앵커에서 CAST(... AS VARCHAR(200)) 로 길이를 넉넉히 잡았습니다. 이유는 5절 실수 2 에서 설명합니다.
WITH RECURSIVE up (id, name, mgr, lvl) AS (
SELECT id, name, mgr, 1
FROM emp
WHERE id = 7
UNION ALL
SELECT e.id, e.name, e.mgr, up.lvl + 1
FROM emp e
JOIN up ON e.id = up.mgr
)
SELECT
lvl
, name
FROM up
ORDER BY lvl;LVL | NAME
----+-------
1 | 강민호
2 | 박팀장
3 | 남본부
4 | 김대표
(4행)앵커는 강민호 한 명이고, 조인 조건이 e.id = up.mgr 로 뒤집혀 상사를 붙입니다. 대표는 mgr 가 NULL 이라 다음 단계에서 붙을 행이 없어 멈춥니다.
WITH RECURSIVE sub (root_id, id) AS (
SELECT id, id
FROM emp
UNION ALL
SELECT s.root_id, e.id
FROM emp e
JOIN sub s ON e.mgr = s.id
)
SELECT
r.name
, COUNT(*) - 1 AS sub_cnt
FROM sub s
JOIN emp r ON r.id = s.root_id
GROUP BY r.id, r.name
ORDER BY r.id;NAME | SUB_CNT
-------+--------
김대표 | 10
남본부 | 5
이본부 | 3
박팀장 | 2
서팀장 | 1
최팀장 | 2
강민호 | 0
노지은 | 0
문서준 | 0
오하늘 | 0
임도윤 | 0
(11행)모든 사원을 각자의 루트로 삼아 (root_id, id) 쌍을 만듭니다. 쌍에는 자기 자신도 한 줄 들어가므로 COUNT(*) - 1 이 하위 인원 수입니다. 김대표의 10명은 나머지 전원입니다.
-- Oracle · Tibero
SELECT
LEVEL
, LPAD(' ', (LEVEL - 1) * 2) || name AS tree_name
, SYS_CONNECT_BY_PATH(name, '/') AS path
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR id = mgr
ORDER SIBLINGS BY name;도식(H2 미지원, 실행 결과 아님). 예제 1 과 같은 데이터로 손으로 그린 표입니다.
LEVEL | TREE_NAME | PATH
------+--------------+-----------------------------
1 | 김대표 | /김대표
2 | 남본부 | /김대표/남본부
3 | 박팀장 | /김대표/남본부/박팀장
4 | 강민호 | /김대표/남본부/박팀장/강민호
4 | 노지은 | /김대표/남본부/박팀장/노지은
3 | 서팀장 | /김대표/남본부/서팀장
4 | 문서준 | /김대표/남본부/서팀장/문서준
2 | 이본부 | /김대표/이본부
3 | 최팀장 | /김대표/이본부/최팀장
4 | 오하늘 | /김대표/이본부/최팀장/오하늘
4 | 임도윤 | /김대표/이본부/최팀장/임도윤LEVEL 은 재귀 CTE 의 lvl 과 같은 값입니다. 경로는 SYS_CONNECT_BY_PATH 가 만들어 주므로 재귀 CTE 처럼 path 컬럼을 직접 이어 붙일 필요가 없습니다.
ORDER SIBLINGS BY name 은 트리 구조를 유지한 채 같은 부모의 형제끼리만 이름순으로 정렬합니다. 그냥 ORDER BY name 을 쓰면 트리 순서가 깨집니다.