CTE (공통 테이블 식)

 

테스트 환경

 - OS : Windows 2008 R2 DataCenter

 - DBMS : SQL Server 2012 Enterprise Edition + SP4

 - 문서 : https://docs.microsoft.com/ko-kr/previous-versions/sql/sql-server-2012/ms175972(v=sql.110)

 

내용

CTE 는 임시로 이름이 지정된 결과 집합(임시테이블 ?) 입니다.

쿼리문에서 반복 사용되는 집합을 CTE 를 이용해 별도 생성하거나 재귀 쿼리가 필요할때 유용합니다.

 

CTE 는 공통 테이블 식과 재귀 공통 테이블 식 이 있습니다.

 

 

구문

[ WITH [ ,...n ] ] ::= expression_name [ ( column_name [ ,...n ] ) ] AS ( CTE_query_definition )     expression_name 공통 테이블 식의 유효한 식별자입니다. expression_name은 같은 WITH 절에서 정의된 다른 공통 테이블 식의 이름과는 달라야 하지만 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 열을 참조하는 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에는 다음 절을 사용할 수 없습니다. ORDER BY(TOP 절을 지정하는 경우는 제외) INTO 쿼리 힌트가 있는 OPTION 절 FOR XML FOR BROWSE 일괄 처리에 속한 문에 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 PIVOT(데이터베이스 호환성 수준이 110인 경우. SQL Server 2012 데이터베이스 엔진 기능의 주요 변경 참조) HAVING 스칼라 집계 TOP LEFT, RIGHT, OUTER JOIN(INNER JOIN이 허용됨) 하위 쿼리 CTE_query_definition 내부의 CTE에 대한 재귀적 참조에 적용되는 힌트

 

 

재귀 공통 테이블 식 정의 및 사용 지침

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

 

예제

1) 간단한 CTE 만들기 USE AdventureWorks2012; GO -- Define the CTE expression name and column list. WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear) AS -- Define the CTE query. ( SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL ) -- Define the outer query referencing the CTE name. SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear FROM Sales_CTE GROUP BY SalesYear, SalesPersonID ORDER BY SalesPersonID, SalesYear; GO