[MySQL] 계층형 조회 (Recursive 재귀 쿼리)

WITH RECURSIVE cte_count 
AS ( 
    -- Non-Recursive 문장( 첫번째 루프에서만 실행됨 )
    SELECT 1 AS n
    UNION ALL
    -- Recursive 문장(읽어 올 때마다 행의 위치가 기억되어 다음번 읽어 올 때 다음 행으로 이동함)
    SELECT n + 1 AS num 
    FROM cte_count
    WHERE n < 3 
)

SELECT * FROM test;

1. 메모리 상에 가상의 테이블을 저장한다.
2. 반드시 UNION 사용해야한다.
3. 반드시 비반복문(Non-Recursive)도 최소한 1개 요구된다. 처음 한번만 실행
4. SubQuery에서 바깥의 가상의 테이블을 참조하는 문장(반복문)이 반드시 필요하다.
5. 반복되는 문장은 반드시 정지조건(Termination condition)이 요구된다.
6. 가상의 테이블을 구성하면서 그 자신(가상의 테이블)을 참조하여 값을 결정할 때 유용한다.

 

<select id="getRoleMenuScrnRoleUse" parameterType="java.util.List"
			resultType="kr.co.common.spring.security.dto.RoleMenuScrnMapg">
-- 메뉴 정보 및 계층 구조를 조회하는 SQL 쿼리
SELECT
    RESULT.MENU_ID, -- 메뉴 아이디
    NVL2(RESULT.HPOS_MENU_ID, RESULT.HPOS_MENU_ID, '#') AS HPOS_MENU_ID, -- 상위 메뉴 아이디
    RESULT.MENU_IDX, -- 메뉴 순서
    RESULT.MENU_ORDER, -- 메뉴 트리 정렬 순서
    RESULT.MENU_NM, -- 메뉴 명
    RESULT.LVL, -- 레벨
    RESULT.MENU_PATH, -- 메뉴 경로
    RESULT.MENU_URL -- 메뉴 링크
FROM (
    -- 재귀적인 CTE (Common Table Expression)를 사용하여 계층적 구조를 조회
    WITH RECURSIVE CTE AS (
        -- 초기 쿼리: 최상위 메뉴부터 시작하여 계층적으로 조회
        SELECT
            MNU.MENU_ID,
            MNU.MENU_NM,
            MNU.HPOS_MENU_ID,
            1 AS LVL,
            MNU.MENU_NM AS MENU_PATH,
            TO_CHAR(MNU.MENU_IDX) AS MENU_ORDER,
            MNU.MENU_PATH AS MENU_URL,
            MNU.MENU_IDX
        FROM
            TB_MENU MNU
        INNER JOIN (
            -- 특정 역할에 속한 메뉴 조회
            SELECT
                MAP.MENU_ID
            FROM
                TB_AUTH_MENU_MAP MAP
            INNER JOIN
                TB_AUTH AUTH ON MAP.AUTH_ID = AUTH.AUTH_ID
            WHERE
                MAP.AUTH_ID IN
                <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
                    #{item.roleId}
                </foreach>
            GROUP BY MAP.MENU_ID
        ) USERMENUS ON MNU.MENU_ID = USERMENUS.MENU_ID
        WHERE
            HPOS_MENU_ID IS NULL

        UNION ALL

        -- 재귀 쿼리: 하위 메뉴들을 계속해서 조회하여 계층적 구조 생성
        SELECT
            B.MENU_ID,
            B.MENU_NM,
            B.HPOS_MENU_ID,
            C.LVL + 1 AS LVL,
            CONCAT(C.MENU_PATH,' > ', B.MENU_NM) AS MENU_PATH,
            CONCAT(C.MENU_ORDER,',', B.MENU_IDX) AS MENU_ORDER,
            B.MENU_PATH AS MENU_URL,
            B.MENU_IDX
        FROM
            TB_MENU B
        INNER JOIN (
            -- 특정 역할에 속한 메뉴 조회
            SELECT
                MAP.MENU_ID
            FROM
                TB_AUTH_MENU_MAP MAP
            INNER JOIN
                TB_AUTH AUTH ON MAP.AUTH_ID = AUTH.AUTH_ID
            WHERE
                MAP.AUTH_ID IN
                <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
                    #{item.roleId}
                </foreach>
            GROUP BY MAP.MENU_ID
        ) USERMENUS ON B.MENU_ID = USERMENUS.MENU_ID
        INNER JOIN CTE C ON B.HPOS_MENU_ID = C.MENU_ID
    )
    -- 최종 결과: CTE에서 계산된 메뉴 정보를 선택
    SELECT
        MENU_ID, -- 메뉴 아이디
        MENU_NM, -- 메뉴 명
        HPOS_MENU_ID, -- 상위 메뉴 아이디
        LVL, -- 레벨
        MENU_PATH, -- 메뉴 경로
        MENU_ORDER, -- 메뉴 트리 정렬 기준
        MENU_URL, -- 메뉴 URL
        MENU_IDX -- 메뉴 순서
    FROM CTE A
) RESULT
-- 메뉴와 권한 매핑 정보를 결합하여 특정 역할이 갖고 있는 메뉴를 조회
LEFT OUTER JOIN
TB_AUTH_MENU_MAP MAP ON RESULT.MENU_ID = MAP.MENU_ID
    AND MAP.AUTH_ID IN
    <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
        #{item.roleId}
    </foreach>
-- 결과를 메뉴 순서(MENU_IDX)로 오름차순 정렬
ORDER BY RESULT.MENU_IDX ASC;

 

 

 

 

 

출처: https://inpa.tistory.com/entry/MYSQL-📚-RECURSIVE-재귀-쿼리 [Inpa Dev 👨‍💻:티스토리]