[MySQL] 재귀 쿼리 (RECURSIVE CTE)

 

재귀 쿼리는 자기 자신을 참조하여 쿼리를 실행하는 것이다.

영어로 Recursive CTE이다.

 

CTE란?

• Common Table Expression의 약자
• WITH 문을 사용하여 만든 임시 테이블
• 하나의 쿼리문 범위 내에서만 존재하며, 여러번 참조될 수 있는 일회성 테이블
• 재사용이 필요한 서브 쿼리를 간결하게 표현할 때 유용

 

[CTE 기본 구조]

WITH CTE_이름 AS (
    -- CTE 내부의 쿼리 정의
    SELECT ...
)
SELECT * FROM CTE_이름

 

 

 

재귀적(Recursive) CTE

• Recursive CTE(재귀 공통 테이블 표현식)는 재귀적으로 데이터를 처리할 수 있는 CTE의 확장 버전
• 주로 계층적 데이터(예: 조직 구조, 트리 구조)를 탐색하거나, 반복적인 계산이 필요한 경우에 사용

 

[기본적인 구조]

-- RECURSIVE CTE
WITH RECURSIVE cte (...) AS 
(
   SELECT ...   -- Non Recursive 부분
   UNION ALL 
   SELECT ...  -- Recursive 부분
)
SELECT * FROM cte;

 

재귀 쿼리는 두개의 SELECT절로 이루어지며 각 SELECT절은 UNION으로 구분된다.

첫번째 SELECT절은 반복을 시작할 첫번째 행을 만드는 역할이고

두번째 SELECT절이 반복적으로 자기자신을 참조하여 쿼리를 실행한다.

 

 

사용 예시

 

부서들을 부모-자식 순으로 계층을 정렬하고, '가,나,다' 순으로 정렬하려 한다. 

 

WITH RECURSIVE DeptHierarchy AS (
    -- 최상위 부서 선택
    SELECT
        Dept_code AS deptId,
        Dept_name AS deptName,
        NULLIF(Pdept_code, '0') AS parentDeptId,
        1 AS depth,
        Dept_name AS fullPath
    FROM
        v_rv_dept
    WHERE
        Pdept_code = '0' OR Pdept_code IS NULL

    UNION ALL

    -- 하위 부서를 재귀적으로 탐색
    SELECT
        child.Dept_code AS deptId,
        child.Dept_name AS deptName,
        NULLIF(child.Pdept_code, '0') AS parentDeptId,
        parent.depth + 1 AS depth,
        CONCAT(parent.fullPath, ' > ', child.Dept_name) AS fullPath
    FROM
        v_rv_dept child
    INNER JOIN
        DeptHierarchy parent
    ON
        child.Pdept_code = parent.deptId // 상위 부서와 연결된 하위 부서 찾기
)
SELECT
    deptId,
    deptName,
    parentDeptId,
    depth,
    fullPath
FROM
    DeptHierarchy
ORDER BY
    fullPath; // 부서 계층 구조 정렬

 

출력 예시

 

데이터 예제 (v_rv_dept):

Dept_code	Dept_name	Pdept_code
1	본사	0
2	연구소	1
3	개발팀	2
4	영업팀	1
5	해외영업팀	4

 

결과:

 

deptId	deptName	parentDeptId	depth	fullPath
1	본사	NULL	1	본사
2	연구소	1	2	본사 > 연구소
3	개발팀	2	3	본사 > 연구소 > 개발팀
4	영업팀	1	2	본사 > 영업팀
5	해외영업팀	4	3	본사 > 영업팀 > 해외영업팀

 

 

 

 

---

https://velog.io/@leo4study/SQL-RECURSIVE-CTE-이해하기

https://wildeveloperetrain.tistory.com/340