[SQL] 계층형 질의 - WITH, CTE

 

테이블에 계층형 데이터가 존재하는 경우 계층형 질의를 통해 데이터를 조회할 수 있다.

계층형 데이터: 동일 테이블에 상위/하위 데이터가 포함되어 있는 데이터 (ex: 사원-관리자)

 

WITH 구문: 서브쿼리에 이름을 붙여 사용할 수 있도록 하는 명령어

CTE = Common Table Expression

 

예시 코드 1

WITH RECURSIVE CTE(member_id, manager_id, lvl)
AS (
	SELECT member_id, manager_id, 0 AS lvl
    FROM MEMBER
    WHERE manager_id IS NULL		// 최상위 레벨의 경우 lvl을 0으로 지정
    
    UNION ALL						// 중복 제거 없이 추가
    
    SELECT M.member_id, M.manager_id, C.lvl + 1
    FROM MEMBER M
    JOIN CTE AS C					// CTE 테이블을 JOIN
    ON M.member_id = C.member_id
)

// 조회
SELECT member_id, manager_id, lvl
FROM CTE
ORDER BY member_id, lvl;

 

예시코드 2

WITH RECURSIVE CTE(lvl, member_id, member_name, manager_id)
AS (
    SELECT 0 as lvl, member_id, member_name, manager_id
    FROM MEMBER
    WHERE manager_id IS NULL

    UNION

    SELECT C.lvl+1, M.member_id, M.member_name, M.manager_id
    FROM MEMBER AS M
    JOIN CTE AS C
    ON M.manager_id = C.member_id
)

SELECT CTE.lvl, CTE.member_name, MEMBER.member_name as manager_name
FROM CTE
LEFT OUTER JOIN MEMBER
ON CTE.manager_id = MEMBER.member_id
ORDER BY CTE.member_id;