WITH common_table_expression(Transact-SQL)

아티클
12/15/2008

CTE(공통 테이블 식)라고도 하는 임시로 이름이 지정된 결과 집합을 지정합니다. CTE는 단순 쿼리에서 파생되며 SELECT, INSERT, UPDATE 또는 DELETE 문 하나의 실행 범위 내에서 정의됩니다. 이 절은 정의하는 SELECT 문의 일부로 CREATE VIEW 문 내에서도 사용할 수 있습니다. 공통 테이블 식은 자신에 대한 참조를 포함할 수 있으며 이를 재귀 공통 테이블 식이라 합니다.

항목 링크 아이콘 Transact-SQL 구문 표기 규칙

구문

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
        expression_name [ ( column_name [ ,...n ] ) ]
    AS
        ( CTE_query_definition )

인수

expression_name
공통 테이블 식의 유효한 식별자입니다. expression_name은 같은 WITH <common_table_expression> 절에서 정의된 다른 공통 테이블 식의 이름과는 달라야 하지만 expression_name이 기본 테이블 또는 뷰의 이름과 같을 수 있습니다. 쿼리에서 expression_name에 대한 모든 참조는 기본 개체가 아니라 공통 테이블 식을 사용합니다.

column_name
공통 테이블 식에서 열 이름을 지정합니다. 단일 CTE 정의 내에서는 중복 이름이 허용되지 않습니다. 지정한 열 이름 수는 반드시 CTE_query_definition의 결과 집합에 있는 열 수와 일치해야 합니다. 열 이름 목록은 쿼리 정의에 모든 결과 열에 대한 고유한 이름을 제공한 경우에만 선택 사항입니다.

CTE_query_definition
공통 테이블 식을 채울 결과 집합을 위한 SELECT 문을 지정합니다. CTE_query_definition의 SELECT 문은 CTE가 또 다른 CTE를 정의하지는 못한다는 점을 제외하고는 뷰를 만들 때와 동일한 요구 사항을 만족해야 합니다. 자세한 내용은 주의 섹션 및 CREATE VIEW(Transact-SQL)를 참조하십시오.

CTE_query_definition을 두 개 이상 정의하는 경우 UNION ALL, UNION, EXCEPT 또는 INTERSECT 집합 연산자 중 하나로 쿼리 정의를 조인해야 합니다. 재귀 CTE 쿼리 정의를 사용하는 방법은 다음 "주의" 섹션과 공통 테이블 식을 사용하는 재귀 쿼리를 참조하십시오.

주의

CTE를 만들고 사용하기 위한 지침

비재귀 CTE에는 다음 지침이 적용됩니다. 재귀 CTE에 적용하는 지침은 다음의 "재귀 CTE를 정의하고 사용하기 위한 지침"을 참조하십시오.

CTE 뒤에는 일부 또는 모든 CTE 열을 참조하는 SELECT, INSERT, UPDATE 또는 DELETE 문 하나가 있어야 합니다. 뷰의 SELECT 문 정의의 일부로 CREATE VIEW 문 내에 CTE를 지정할 수 있습니다.
비재귀 CTE 내에 여러 개의 CTE 쿼리 정의를 정의할 수 있습니다. UNION ALL, UNION, INTERSECT 또는 EXCEPT 집합 연산자 중 하나를 사용해 이 같은 정의를 결합해야 합니다.
CTE는 같은 WITH 절에서 자신 및 이전에 정의한 CTE를 참조할 수 있지만 전방 참조는 허용되지 않습니다.
CTE에 둘 이상의 WITH 절을 지정할 수 없습니다. 예를 들어 CTE_query_definition이 하위 쿼리를 포함하는 경우 그 하위 쿼리가 또 다른 CTE를 정의하는 중첩 WITH 절을 포함할 수 없습니다.
CTE_query_definition에는 다음 절을 사용할 수 없습니다.
- COMPUTE 또는 COMPUTE BY
- ORDER BY(TOP 절을 지정하는 경우는 제외)
- INTO
- 쿼리 힌트가 있는 OPTION 절
- FOR XML
- FOR BROWSE
일괄 처리에 속한 문에 CTE를 사용할 때는 그 전의 문 다음에 반드시 세미콜론을 추가해야 합니다.
CTE를 참조하는 쿼리를 사용하여 커서를 정의할 수 있습니다.
CTE에서 원격 서버상의 테이블을 참조할 수 있습니다.
CTE를 실행할 때는 쿼리의 뷰를 참조하는 힌트와 마찬가지로 CTE를 참조하는 힌트가 CTE가 기본 테이블에 액세스할 때 발견되는 다른 힌트와 충돌을 일으킬 수 있습니다. 이러한 충돌이 발생하는 경우 쿼리에서 오류를 반환합니다. 자세한 내용은 뷰 확인을 참조하십시오.

재귀 CTE를 정의하고 사용하기 위한 지침

재귀 CTE를 정의할 때는 다음 지침이 적용됩니다.

재귀 CTE 정의는 적어도 하나의 앵커 멤버와 하나의 재귀 멤버로 두 개의 CTE 쿼리 정의를 포함해야 합니다. 앵커 멤버와 재귀 멤버를 여러 개 정의할 수 있지만 앵커 멤버 쿼리 정의는 모두 첫 번째 재귀 멤버 정의 앞에 와야 합니다. 모든 CTE 쿼리 정의는**CTE 자체를 참조하지 않는 한 앵커 멤버입니다.
앵커 멤버는 UNION ALL, UNION, INTERSECT 또는 EXCEPT 집합 연산자 중 하나를 이용해 결합해야 합니다. UNION ALL은 여러 재귀 멤버를 결합할 때 마지막 앵커 멤버와 첫 번째 재귀 멤버 사이에서 허용되는 유일한 집합 연산자입니다.
앵커 멤버 및 재귀 멤버에 있는 열의 수는 같아야 합니다.
재귀 멤버에 있는 열의 데이터 형식은 앵커 멤버에 있는 해당 열의 데이터 형식과 반드시 같아야 합니다.
재귀 멤버의 FROM 절은 CTE expression_name을 한 번만 참조해야 합니다.
다음 항목은 재귀 멤버의 CTE_query_definition에서 허용되지 않습니다.
- SELECT DISTINCT
- GROUP BY
- HAVING
- 스칼라 집계
- TOP
- LEFT, RIGHT, OUTER JOIN(INNER JOIN이 허용됨)
- 하위 쿼리
- CTE_query_definition 내부의 CTE에 대한 재귀적 참조에 적용되는 힌트

재귀 CTE를 사용할 때는 다음 지침이 적용됩니다.

재귀 CTE가 반환하는 모든 열은 참가하는 SELECT 문이 반환하는 열의 Null 허용 여부와는 상관없이 Null을 허용합니다.
잘못 구성된 재귀 CTE로 인해 무한 루프가 발생할 수 있습니다. 예를 들어 재귀 멤버 쿼리 정의가 부모 열과 자식 열에 대해 모두 같은 값을 반환하면 무한 루프가 생성된 것입니다. 무한 루프를 막기 위해서는 INSERT, UPDATE, DELETE 또는 SELECT 문의 OPTION 절에서 MAXRECURSION 힌트와 0부터 32,767 사이의 값을 사용하여 특정 문에 허용되는 재귀 수준을 제한할 수 있습니다. 이 방법으로 루프를 발생시키는 코드 문제를 해결할 때까지 문의 실행을 제어할 수 있습니다. 서버 차원의 기본값은 100입니다. 0을 지정하면 아무런 한계도 적용되지 않습니다. 하나의 문에는 하나의 MAXRECURSION 값만 지정할 수 있습니다. 자세한 내용은 쿼리 힌트(Transact-SQL)를 참조하십시오.
재귀 공통 테이블 식을 포함한 뷰를 사용하여 데이터를 업데이트할 수 없습니다.
CTE를 사용하여 쿼리에 커서를 정의할 수 있습니다. CTE는 커서의 결과 집합을 정의하는 select_statement 인수입니다. 재귀 CTE에는 빠른 전진 전용 커서 및 정적(스냅숏) 커서만 사용할 수 있습니다. 재귀 CTE에 또 다른 커서 유형을 지정하는 경우 해당 커서 유형이 정적으로 변환됩니다.
CTE에서 원격 서버상의 테이블을 참조할 수 있습니다. CTE의 재귀 멤버에서 원격 서버를 참조하는 경우 로컬에서 반복적으로 테이블에 액세스할 수 있도록 각 원격 테이블을 위한 스풀이 생성됩니다.

예

1. 간단한 공통 테이블 식 만들기

다음 예에서는 Adventure Works Cycles의 각 관리자에게 직접 보고하는 직원 수를 보여 줍니다.

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) 
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT ManagerID, DirectReports 
FROM DirReps 
ORDER BY ManagerID;
GO

2. 공통 테이블 식을 사용한 수 제한 및 평균 보고

다음 예에서는 관리자에게 보고하는 평균 직원 수를 보여 줍니다.

WITH DirReps (Manager, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) AS DirectReports
    FROM HumanResources.Employee
    GROUP BY ManagerID
) 
SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
FROM DirReps 
WHERE DirectReports>= 2 ;
GO

3. 공통 테이블 식을 두 번 이상 참조

다음 예에서는 SalesOrderHeader 테이블에서 각 판매 직원의 총 판매 주문 수와 가장 최근의 판매 주문 날짜를 보여 줍니다. 실행되는 문은 다음과 같이 두 차례 CTE를 참조하는데 첫 번째는 판매 직원에 대해 선택한 열을 반환하기 위해서이며 두 번째는 해당 판매 직원의 관리자에 대한 비슷한 세부 정보를 검색하기 위해서입니다. 판매 직원 및 관리자에 대한 데이터는 모두 단일 행으로 반환됩니다.

USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

4. 재귀 공통 테이블 식을 사용하여 여러 수준의 재귀 표시

다음 예에서는 관리자의 계층적 목록 및 이들에게 보고하는 직원을 보여 줍니다.

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports ;
GO

5. 재귀 공통 테이블 식을 사용하여 두 가지 수준의 재귀 표시

다음 예에서는 관리자와 그들에게 보고하는 직원을 보여 줍니다. 반환되는 수준은 2로 제한됩니다.

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports 
WHERE EmployeeLevel <= 2 ;
GO

6. 재귀 공통 테이블 식을 사용하여 계층적 목록 표시

다음 예에서는 3번 예를 바탕으로 관리자 및 직원의 이름과 각각의 직함을 추가하는 방법을 보여 줍니다. 관리자와 직원의 계층을 추가로 강조하기 위해 각각의 수준을 들여쓰기하였습니다.

USE AdventureWorks;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
    FROM HumanResources.Employee as e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort;
GO

7. MAXRECURSION을 사용하여 문 취소

잘못 구성된 재귀 CTE가 무한 루프에 진입하는 것을 방지하는 데 MAXRECURSION을 사용할 수 있습니다. 다음 예에서는 의도적으로 무한 루프를 만들고 MAXRECURSION 힌트를 사용하여 재귀 수준을 2로 제한하는 방법을 보여 줍니다.

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

코딩 오류를 교정한 다음에는 더 이상 MAXRECURSION이 필요하지 않습니다. 다음 예에서는 교정된 코드를 보여 줍니다.

USE AdventureWorks;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM HumanResources.Employee AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

8. 공통 테이블 식을 사용하여 SELECT 문에서 재귀적 관계를 선택적으로 단계별 진행

다음 예에서는 ProductAssemblyID = 800 자전거를 제작하는 데 필요한 부품과 구성 요소의 계층을 보여 줍니다.

USE AdventureWorks;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom 
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
        ComponentLevel 
FROM Parts AS p
    INNER JOIN Production.Product AS pr
    ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO

9. UPDATE 문에서 재귀 CTE 사용

다음 예에서는 ManagerID 12에게 직접 또는 간접적으로 보고하는 모든 직원의 VacationHours 값을 25% 업데이트하는 방법을 보여 줍니다. 공통 테이블 식은 ManagerID 12에게 직접 보고하는 직원과 그 직원에게 보고하는 직원 식으로 이어지는 계층적 목록을 반환하며 이렇게 공통 테이블 식이 반환한 행에만 업데이트를 적용합니다.

USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO

10. 여러 앵커 및 재귀 멤버 사용

다음 예에서는 지정된 인물의 모든 조상을 반환하기 위해 여러 개의 앵커 및 재귀 멤버를 사용하는 방법을 보여 줍니다. 이 예는 재귀 CTE가 반환한 가족 계보를 구성하기 위해 하나의 테이블을 만들고 값을 삽입합니다.

-- Genealogy table
IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person;
GO
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(7, 'Bill', 5, 4);
GO
-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation (ID) AS
(
-- First anchor member returns Bonnie's mother.
    SELECT Mother 
    FROM Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father 
    FROM Person
    WHERE Name = 'Bonnie'
UNION ALL
-- First recursive member returns male ancestors of the previous generation.
    SELECT Person.Father
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.
    SELECT Person.Mother
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;
GO

참고 항목

참조

CREATE VIEW(Transact-SQL)
DELETE(Transact-SQL)
EXCEPT 및 INTERSECT(Transact-SQL)
INSERT(Transact-SQL)
SELECT(Transact-SQL)
UPDATE(Transact-SQL)

도움말 및 정보

SQL Server 2005 지원 받기

다음을 통해 공유