공통 테이블 식을 사용하는 재귀 쿼리
CTE(공통 테이블 식)를 사용하면 자기 자신을 참조하는 재귀적 CTE를 만들 수 있으므로 상당히 유용합니다. 재귀적 CTE는 최초 CTE가 반복적으로 실행되어 전체 결과 집합을 얻을 때까지 데이터의 하위 집합을 반환하는 CTE입니다.
재귀적 CTE를 참조하는 쿼리를 재귀 쿼리라고 합니다. 재귀 쿼리의 일반적인 용도는 계층적 데이터를 반환하는 것입니다. 예를 들어 직원을 조직도에 표시하는 경우 또는 부모 제품에 하나 이상의 구성 요소가 있고 이러한 구성 요소가 하위 구성 요소를 가지거나 다른 부모의 구성 요소일 수도 있는 제품 구성 정보(BOM) 시나리오에 데이터를 표시하는 경우가 있습니다.
재귀적 CTE는 SELECT, INSERT, UPDATE, DELETE 또는 CREATE VIEW 문 내에서 재귀 쿼리를 실행하는 데 필요한 코드를 상당히 단순화할 수 있습니다. 이전 버전의 SQL Server에서 재귀 쿼리를 실행하려면 일반적으로 임시 테이블, 커서 및 논리를 사용하여 재귀 단계의 흐름을 제어해야 합니다. 공통 테이블 식에 대한 자세한 내용은 공통 테이블 식 사용을 참조하십시오.
재귀적 CTE 구조
Transact-SQL의 재귀적 CTE 구조는 다른 프로그래밍 언어의 재귀 루틴과 비슷합니다. 다른 언어의 재귀 루틴은 스칼라 값을 반환하지만 재귀적 CTE는 여러 행을 반환할 수 있습니다.
재귀적 CTE는 다음 세 요소로 구성됩니다.
루틴의 호출
재귀적 CTE의 첫 번째 호출은 UNION ALL, UNION, EXCEPT 또는 INTERSECT 연산자로 조인된 하나 이상의 CTE_query_definitions로 구성됩니다. 이러한 쿼리 정의는 CTE 구조의 기본 결과 집합을 형성하기 때문에 앵커 멤버라고 합니다.
CTE_query_definitions는 CTE 자체를 참조하지 않는 경우 앵커 멤버로 간주됩니다. 모든 앵커 멤버 쿼리 정의를 첫 번째 재귀 멤버 정의 앞에 배치하고 UNION ALL 연산자를 사용하여 마지막 앵커 멤버를 첫 번째 재귀 멤버와 조인해야 합니다.
루틴의 재귀 호출
재귀 호출에는 CTE 자체를 참조하는 UNION ALL 연산자로 조인된 하나 이상의 CTE_query_definitions가 포함됩니다. 이러한 쿼리 정의를 재귀 멤버라고 합니다.
종료 확인
종료 확인은 암시적으로 수행됩니다. 이전 호출에서 반환되는 행이 없을 때 재귀가 중지됩니다.
[!참고]
잘못 구성된 재귀적 CTE로 인해 무한 루프가 발생할 수 있습니다. 예를 들어 재귀 멤버 쿼리 정의가 부모 열과 자식 열 모두에 대해 동일한 값을 반환하면 무한 루프가 생성된 것입니다. 재귀 쿼리의 결과를 테스트할 때 INSERT, UPDATE, DELETE 또는 SELECT 문의 OPTION 절에서 MAXRECURSION 힌트 및 0과 32,767 사이의 값을 사용하여 특정 문에 허용되는 재귀 수준의 수를 제한할 수 있습니다. 자세한 내용은 쿼리 힌트(Transact-SQL) 및 WITH common_table_expression(Transact-SQL)을 참조하십시오.
의사 코드 및 의미 체계
재귀적 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
재귀 실행의 의미 체계는 다음과 같습니다.
CTE 식을 앵커 멤버와 재귀 멤버로 분할합니다.
앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합(T0)을 만듭니다.
Ti는 입력으로 사용하고 Ti+1은 출력으로 사용하여 재귀 멤버를 실행합니다.
빈 집합이 반환될 때까지 3단계를 반복합니다.
결과 집합을 반환합니다. 이것은 T0에서 Tn까지의 UNION ALL입니다.
예
다음 예에서는 Adventure Works Cycles 회사에서 직급이 가장 높은 직원부터 시작되는 계층적 직원 목록을 반환하여 재귀적 CTE 구조의 의미 체계를 보여 줍니다. CTE를 실행하는 문은 결과 집합을 Research and Development Group의 직원으로 제한합니다. 이 예 다음에는 코드 실행 연습이 있습니다.
USE AdventureWorks;
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 HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID 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 HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0;
GO
코드 연습 예
재귀적 CTE인 DirectReports는 앵커 멤버와 재귀 멤버를 하나씩 정의합니다.
앵커 멤버가 기본 결과 집합 T0을 반환합니다. 이 직원은 회사에서 직급이 가장 높습니다. 즉, 상급 관리자가 없습니다.
다음은 앵커 멤버에서 반환하는 결과 집합입니다.
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ NULL 109 Chief Executive Officer 0재귀 멤버는 앵커 멤버 결과 집합에 있는 직원의 직속 하급자를 반환합니다. 이것은 Employee 테이블과 DirectReports CTE 간의 조인 작업을 통해 수행됩니다. 재귀 호출을 설정하는 것은 바로 CTE 자신에 대한 이 참조입니다. 입력(Ti)으로 사용된 CTE DirectReports 의 직원을 기준으로 조인(Employee.ManagerID = DirectReports.EmployeeID)은 (Ti)를 관리자로 둔 직원을 출력(Ti+1)으로 반환합니다. 따라서 재귀 멤버의 첫 번째 반복은 다음 결과 집합을 반환합니다.
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 109 12 Vice President of Engineering 1재귀 멤버가 반복적으로 활성화됩니다. 재귀 멤버의 두 번째 반복에서 3단계의 단일 행 결과 집합(EmployeeID12 포함)이 입력 값으로 사용되어 다음 결과 집합을 반환합니다.
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 12 3 Engineering Manager 2재귀 멤버의 세 번째 반복에서 위의 단일 행 결과 집합(EmployeeID3) 포함)이 입력 값으로 사용되어 다음 결과 집합을 반환합니다.
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 3 4 Senior Tool Designer 3 3 9 Design Engineer 3 3 11 Design Engineer 3 3 158 Research and Development Manager 3 3 263 Senior Tool Designer 3 3 267 Senior Design Engineer 3 3 270 Design Engineer 3재귀 멤버의 네 번째 반복에서 EmployeeID 값 4, 9, 11, 158, 263, 267 및 270에 대한 이전 행 집합이 입력 값으로 사용됩니다.
재귀 멤버가 빈 결과 집합을 반환할 때까지 이 프로세스가 반복됩니다.
실행 중인 쿼리가 반환하는 최종 결과 집합은 앵커 멤버와 재귀 멤버가 생성한 모든 결과 집합의 합집합입니다.
다음은 이 예에서 반환되는 전체 결과 집합입니다.
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ NULL 109 Chief Executive Officer 0 109 12 Vice President of Engineering 1 12 3 Engineering Manager 2 3 4 Senior Tool Designer 3 3 9 Design Engineer 3 3 11 Design Engineer 3 3 158 Research and Development Manager 3 3 263 Senior Tool Designer 3 3 267 Senior Design Engineer 3 3 270 Design Engineer 3 263 5 Tool Designer 4 263 265 Tool Designer 4 158 79 Research and Development Engineer 4 158 114 Research and Development Engineer 4 158 217 Research and Development Manager 4 (15 row(s) affected)