CTE(공통 테이블 식)는 SELECT, INSERT, UPDATE, DELETE 또는 CREATE VIEW 문 하나의 실행 범위 내에서 정의되는 임시 결과 집합이라고 볼 수 있습니다. CTE는 개체로 저장되지 않고 쿼리 지속 시간 동안만 존재한다는 점에서 파생 테이블과 비슷합니다. 그러나 CTE는 파생 테이블과 달리 자체 참조가 가능하며 동일 쿼리에서 여러 번 참조될 수 있습니다.
CTE를 사용하여 다음을 수행할 수 있습니다.
- 재귀 쿼리를 만들 수 있습니다. 자세한 내용은 공통 테이블 식을 사용하는 재귀 쿼리를 참조하십시오.
- 일반적인 뷰 사용이 필요하지 않을 때, 즉 메타데이터에 정의를 저장할 필요가 없을 때 뷰를 대체할 수 있습니다.
- 스칼라 하위 SELECT에서 파생된 열 또는 비결정적이거나 외부 액세스가 없는 함수를 기준으로 그룹화할 수 있습니다.
- 동일 문에서 결과 테이블을 여러 번 참조할 수 있습니다.
CTE를 사용하면 가독성이 향상되고 복잡한 쿼리를 쉽게 유지 관리할 수 있는 이점이 있습니다. 쿼리를 개별적이고 단순한 논리적 구성 블록으로 나눌 수 있습니다. 그런 다음 이 단순한 블록을 사용하여 최종 결과 집합이 생성될 때까지 보다 복잡한 중간 CTE를 작성할 수 있습니다.
CTE는 함수, 저장 프로시저, 트리거 또는 뷰 같은 사용자 정의 루틴에서 정의될 수 있습니다.
CTE의 구조 CTE는 CTE를 나타내는 식 이름, 선택적인 열 목록 및 CTE를 정의하는 쿼리로 구성되어 있습니다. CTE를 정의한 후에는 SELECT, INSERT, UPDATE 또는 DELETE 문에서 테이블이나 뷰처럼 참조할 수 있습니다. CTE는 CREATE VIEW 문에서 정의하는 SELECT 문의 일부분으로 사용될 수도 있습니다.
CTE의 기본 구문 구조는 다음과 같습니다.
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
모든 결과 열에 대한 고유 이름이 쿼리 정의에 제공된 경우에만 열 이름 목록이 선택 사항입니다.
CTE를 실행하는 문은 다음과 같습니다.
SELECT <column_list>
FROM expression_name
예
다음 예에서는 CTE 구조의 구성 요소인 식 이름, 열 목록 및 쿼리를 보여 줍니다. CTE 식 Sales_CTE에는 3개의 열(SalesPersonID, NumberOfOrders 및 MaxDate)이 있으며 각 영업 사원의 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
다음은 결과 집합의 일부입니다.
EmployeeID NumberOfOrders MaxDate ManagerID NumberOfOrders MaxDate ----------- -------------- ---------- --------- -------------- ---------- 268 48 2004-06-01 273 NULL NULL 275 450 2004-06-01 268 48 2004-06-01 276 418 2004-06-01 268 48 2004-06-01 277 473 2004-06-01 268 48 2004-06-01
'Dev > SQL' 카테고리의 다른 글
| How to Configure MSDTC to Use a Specific Port in Windows Server 2012/2012R2 (0) | 2014.07.03 |
|---|---|
| SQL Server CLR - 레지스트리 읽기 (0) | 2008.10.10 |
| 테이블 변수 (0) | 2008.07.04 |