[DATABASE/SQLSERVER] CTE 재귀 호출 사용하기

■ CTE 재귀 호출을 사용하는 방법을 보여준다.

▶ 예제 코드 (SQL)


WITH CTE(ParentID, ID, Name, DisplayOrder, MenuLevel)
AS
(
    SELECT
        ----------------------------------------
        A.ParentID
       ,A.ID
       ,A.Name
       ,A.DisplayOrder
       ----------------------------------------
       ,1 AS MenuLevel
    FROM Menu AS A
    WHERE A.ParentID = -1
    UNION ALL
    SELECT
        ----------------------------------------
        A.ParentID
       ,A.ID
       ,A.Name
       ,A.DisplayOrder
        ----------------------------------------
       ,B.MenuLevel + 1
    FROM Menu AS A, CTE AS B
    WHERE A.ParentID = B.ID
)
SELECT ParentID, ID, Name, DisplayOrder FROM CTE
ORDER BY MenuLevel;

WITH CTE(ParentID, ID, Name, DisplayOrder, MenuLevel)

(

SELECT

----------------------------------------

A.ParentID

,A.ID

,A.Name

,A.DisplayOrder

----------------------------------------

,1 AS MenuLevel

FROM Menu AS A

WHERE A.ParentID = -1

UNION ALL

SELECT

----------------------------------------

A.ParentID

,A.ID

,A.Name

,A.DisplayOrder

----------------------------------------

,B.MenuLevel + 1

FROM Menu AS A, CTE AS B

WHERE A.ParentID = B.ID

)

SELECT ParentID, ID, Name, DisplayOrder FROM CTE

ORDER BY MenuLevel;