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

icodebroker SQLSERVER 2024-06-18

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

▶ 예제 코드 (SQL)


WITH CTE (ParentID, ID, [Level], [Path])
AS
(
    SELECT
        A.ParentID                           AS ParentID,
        A.ID                                 AS ID      ,
        1                                    AS [Level] ,
        CONVERT(NVARCHAR(MAX), '/' + A.Name) AS [Path]
    FROM Menu AS A WITH(NOLOCK)
    WHERE A.ParentID = -1

    UNION ALL

    SELECT
        A.ParentID                                      AS ParentID,
        A.ID                                            AS ID      ,
        B.[Level] + 1                                   AS [Level] ,
        CONVERT(NVARCHAR(MAX), B.[Path] + '/' + A.Name) AS [Path]
    FROM Menu AS A WITH(NOLOCK)
    JOIN CTE  AS B ON B.ID = A.ParentID
)
SELECT * FROM CTE AS A ORDER BY A.[Path];

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

WITH CTE (ParentID, ID, [Level], [Path])

AS

(

SELECT

A.ParentID AS ParentID,

A.ID AS ID ,

1 AS [Level] ,

CONVERT(NVARCHAR(MAX), '/' + A.Name) AS [Path]

FROM Menu AS A WITH(NOLOCK)

WHERE A.ParentID = -1

UNION ALL

SELECT

A.ParentID AS ParentID,

A.ID AS ID ,

B.[Level] + 1 AS [Level] ,

CONVERT(NVARCHAR(MAX), B.[Path] + '/' + A.Name) AS [Path]

FROM Menu AS A WITH(NOLOCK)

JOIN CTE AS B ON B.ID = A.ParentID

)

SELECT * FROM CTE AS A ORDER BY A.[Path];