[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;