공통 테이블 식을 사용하는 재귀 쿼리(Transact-SQL)

적용 대상:SQL 서버Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics분석 플랫폼 시스템(PDW)Microsoft Fabric의 SQL 데이터베이스

CTE(공통 테이블 식)는 자신을 참조할 수 있다는 상당한 이점을 제공하므로 재귀 CTE를 만듭니다. 재귀 CTE는 전체 결과 집합을 가져올 때까지 데이터의 하위 집합을 반환하기 위해 초기 CTE가 반복적으로 실행되는 CTE입니다.

재귀 CTE를 참조할 때 쿼리를 재귀 쿼리라고 합니다. 계층적 데이터를 반환하는 것은 재귀 쿼리의 일반적인 사용입니다. 예를 들어 조직도에 직원을 표시하거나 부모 제품에 하나 이상의 구성 요소가 있고 해당 구성 요소에 하위 구성 요소가 있거나 다른 부모의 구성 요소일 수 있는 자료 청구서 시나리오의 데이터를 표시합니다.

재귀 CTE는 , , SELECTINSERT또는 UPDATE 문 내에서 DELETECREATE VIEW재귀 쿼리를 실행하는 데 필요한 코드를 크게 간소화할 수 있습니다. 이전 버전의 SQL Server에서 재귀 쿼리는 일반적으로 임시 테이블, 커서 및 논리를 사용하여 재귀 단계의 흐름을 제어해야 합니다. 일반적인 테이블 식에 대한 자세한 내용은 WITH common_table_expression 참조하세요.

Microsoft Fabric에서 Fabric Data Warehouse와 SQL 분석 엔드포인트는 모두 표준, 순차 및 중첩된 CTE를 지원하지만 재귀 CTE는 지원하지 않습니다.

재귀 CTE의 구조

Transact-SQL 재귀 CTE의 구조는 다른 프로그래밍 언어의 재귀 루틴과 유사합니다. 다른 언어의 재귀 루틴은 스칼라 값을 반환하지만 재귀 CTE는 여러 행을 반환할 수 있습니다.

재귀 CTE는 다음 세 가지 요소로 구성됩니다.

1. 루틴의 호출입니다.

   재귀 CTE의 첫 번째 호출은 , 또는 UNION ALLUNION 연산자가 조인EXCEPT한 하나 이상의 CTE 쿼리 정의로 INTERSECT구성됩니다. 이러한 쿼리 정의는 CTE 구조의 기본 결과 집합을 형성하므로 앵커 멤버라고 합니다.

   CTE 쿼리 정의는 CTE 자체를 참조하지 않는 한 앵커 멤버로 간주됩니다. 모든 앵커 멤버 쿼리 정의는 첫 번째 재귀 멤버 정의 앞에 배치되어야 하며, 마지막 앵커 멤버를 UNION ALL 첫 번째 재귀 멤버와 조인하는 데 연산자를 사용해야 합니다.

2. 루틴의 재귀 호출입니다.

   재귀 호출에는 CTE 자체를 참조하는 연산자가 조인한 UNION ALL 하나 이상의 CTE 쿼리 정의가 포함됩니다. 이러한 쿼리 정의를 재귀 멤버라고 합니다.

3. 종료 확인.

   종료 검사는 암시적입니다. 이전 호출에서 반환된 행이 없으면 재귀가 중지됩니다.

자세한 내용은 다음을 참조하세요.

• 쿼리 힌트
• common_table_expression
• 재귀 CTE

의사 코드 및 의미 체계

재귀 CTE 구조체에는 하나 이상의 앵커 멤버와 하나의 재귀 멤버가 포함되어야 합니다. 다음 의사 코드는 단일 앵커 멤버와 단일 재귀 멤버를 포함하는 간단한 재귀 CTE의 구성 요소를 보여 줍니다.

WITH cte_name ( column_name [ ,...n ] )
AS
(
    CTE_query_definition -- Anchor member is defined.
    UNION ALL
    CTE_query_definition -- Recursive member is defined referencing cte_name.
)

-- Statement using the CTE
SELECT *
FROM cte_name

재귀 실행의 의미 체계는 다음과 같습니다.

1. CTE 식을 앵커 및 재귀 멤버로 분할합니다.
2. 첫 번째 호출 또는 기본 결과 집합(T0)을 만드는 앵커 멤버를 실행합니다.
3. 입력 Ti 으로 재귀 멤버를 Ti 실행하고+ 1을 출력으로 실행합니다.
4. 빈 집합이 반환될 때까지 3단계를 반복합니다.
5. 결과 집합을 반환합니다. 이것은 UNION ALL 할 일입니다 T0Tn.

Examples

다음 예제에서는 데이터베이스에서 가장 높은 순위의 직원부터 시작하여 직원의 계층적 목록을 반환하여 재귀 CTE 구조의 의미 체계를 AdventureWorks2025 보여 줍니다. 코드 실행 연습은 예제를 따릅니다.

직원 테이블을 만듭니다.

CREATE TABLE dbo.MyEmployees
(
    EmployeeID SMALLINT NOT NULL,
    FirstName NVARCHAR (30) NOT NULL,
    LastName NVARCHAR (40) NOT NULL,
    Title NVARCHAR (50) NOT NULL,
    DeptID SMALLINT NOT NULL,
    ManagerID INT NULL,
    CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);

테이블을 값으로 채웁다.

INSERT INTO dbo.MyEmployees
VALUES
    (1, N'Ken', N'Sánchez', N'Chief Executive Officer', 16, NULL),
    (273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1),
    (274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273),
    (275, N'Michael', N'Blythe', N'Sales Representative', 3, 274),
    (276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274),
    (285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273),
    (286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285),
    (16, N'David', N'Bradley', N'Marketing Manager', 4, 273),
    (23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

USE AdventureWorks2008R2;
GO

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS (
-- Anchor member definition
SELECT e.ManagerID,
           e.EmployeeID,
           e.Title,
           edh.DepartmentID,
           0 AS Level
    FROM dbo.MyEmployees AS e
         INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
             ON e.EmployeeID = edh.BusinessEntityID
            AND edh.EndDate IS NULL
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.ManagerID,
           e.EmployeeID,
           e.Title,
           edh.DepartmentID,
           Level + 1
    FROM dbo.MyEmployees AS e
         INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
             ON e.EmployeeID = edh.BusinessEntityID
            AND edh.EndDate IS NULL
         INNER JOIN DirectReports AS d
             ON e.ManagerID = d.EmployeeID)
-- Statement that executes the CTE
SELECT ManagerID,
       EmployeeID,
       Title,
       DeptID,
       Level
FROM DirectReports
     INNER JOIN HumanResources.Department AS dp
         ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing'
      OR Level = 0;
GO

예제 코드 연습

재귀 CTE DirectReports는 하나의 앵커 멤버와 하나의 재귀 멤버를 정의합니다.

앵커 멤버는 기본 결과 집합을 반환합니다 T0. 이것은 회사에서 가장 높은 순위의 직원입니다. 즉, 관리자에게 보고하지 않는 직원입니다.

앵커 멤버가 반환한 결과 집합은 다음과 같습니다.

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer        0

재귀 멤버는 앵커 멤버 결과 집합에서 직원의 직접 종속자를 반환합니다. 이는 Employee 테이블과 CTE 간의 조인 작업을 통해 DirectReports 수행됩니다. 재귀 호출을 설정하는 것은 CTE 자체에 대한 이 참조입니다. CTE DirectReports 의 직원을 입력(Ti)으로 기준으로 조인(MyEmployees.ManagerID = DirectReports.EmployeeID)은 출력(Ti + 1)으로 반환하고 관리자로 있는 직원(Ti)을 반환합니다.

따라서 재귀 멤버의 첫 번째 반복은 다음 결과 집합을 반환합니다.

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
1         273        Vice President of Sales       1

재귀 멤버는 반복적으로 활성화됩니다. 재귀 멤버의 두 번째 반복에서는 3단계의 단일 행 결과 집합(of EmployeeID포함)을 273 입력 값으로 사용하고 이 결과 집합을 반환합니다.

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2

재귀 멤버의 세 번째 반복에서는 이전 결과 집합을 입력 값으로 사용하고 다음 결과 집합을 반환합니다.

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3

실행 중인 쿼리에서 반환되는 최종 결과 집합은 앵커 및 재귀 멤버에 의해 생성된 모든 결과 집합의 합치입니다.

결과 집합은 다음과 같습니다.

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer       0
1         273        Vice President of Sales       1
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3

관련 콘텐츠

• WITH common_table_expression(Transact-SQL)
• 쿼리 힌트(Transact SQL)
• INSERT(Transact-SQL)
• UPDATE(Transact-SQL)
• 삭제 (Transact-SQL)
• EXCEPT 및 INTERSECT(Transact-SQL)
• 재귀 CTE