☞ CTE(Common Table Expression)란?
임시로 이름이 지정된 결과 집합이다.
하위에서 서브쿼리 형식으로 사용할 부분을 미리 정의해 놓은것이다.
☞ 구문
WITH CTE명(컬럼, 컬럼) AS
(
CTE 쿼리 정의
)
CTE를 포함하여 실행할 쿼리
☞ 주의
CTE를 정의하면 쿼리에서 여러 차례 참조할 수 있다.
참조 여부와 상관없이 CTE 정의 다음에 외부 쿼리가 나와야 한다.
하나의 WITH절에 여러개의 CTE를 정의할수 있다. (컴마로 구분)
이전에 정의된 CTE를 참조하는 CTE를 정의할 수 있다.
그러나 이후에 정의된 CTE는 참조할 수 없다. (훔.... 예제에서 다시 설명하겠다!)
☞ 사용 예제
1. 한개의 CTE 정의
USE AdventureWorks;
GO
WITH DirReps_CTE(ManagerID, DirectReports) AS
(
SELECT ManagerID, COUNT(*)
FROM HumanResources.Employee AS e
WHERE ManagerID IS NOT NULL
GROUP BY ManagerID
)
SELECT ManagerID, DirectReports
FROM DirReps_CTE
WHERE ManagerID <= 50
ORDER BY ManagerID;
CTE를 정의하고 하위에서 검색조건, 정렬조건을 다시 지정하거나
데이터를 가공하여 조회 할 수 있다.
2. 두개 이상의 CTE 정의
USE AdventureWorks;
GO
WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
SELECT EmployeeID, COUNT(*)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
),
MinMaxCTE(MN, MX, Diff)
AS
(
SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
FROM EmpOrdersCTE
)
SELECT * FROM MinMaxCTE
EmpOrdersCTE : EmployeeID별 주문수 목록
MinMaxCTE : EmpOrdersCTE를 참조하여 최소, 최대 주문수와 차이
마지막 쿼리에서 MinMaxCTE를 참조하여 최소, 최대, 차이를 구할수 있다.
이전에 정의된 CTE는 하위에서 참조할 수 있지만 반대로 이후에 정의된 CTE는 참조할 수 없다.
예를 들어 WITH 문에 CTE1, CTE2, CTE3를 정의할때
CTE2는 CTE1과 CTE2를 참조할 수 있지만 CTE3는 참조할 수 없다.
다음 쿼리를 실행하믄 위와같은 이유로 오류가 발생한다.
WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
SELECT EmployeeID, COUNT(*)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
),
MinCTE(MN)
AS
(
-- MinMaxCTE가 정의되기 전에 참조할수 없다.
SELECT MN
FROM MinMaxCTE
),
MinMaxCTE(MN, MX, Diff)
AS
(
SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
FROM EmpOrdersCTE
)
SELECT * FROM MinMaxCTE
재귀쿼리나... 다양한 예제를 보려면 다음을 참조하자 (귀차니즘.. -_-)
[출처] [MSSQL2005] CTE(공통 테이블 식)|작성자 필립박
'데이터베이스 > SQL Server' 카테고리의 다른 글
| MSSQL2005에 등장한 각종 순위 함수 (0) | 2008.04.28 |
|---|---|
| 인덱스 다시 구성 및 다시 작성 mssql2005 (0) | 2008.04.28 |
| [MSSQL2005] 스키마테이블을 이용하여 테이블 정보 알아내기 (0) | 2008.04.28 |
| 특정 기간(datetime형식)을 주어 조회할 경우 주의 (0) | 2008.04.28 |
| [MSSQL2005] LIKE 검색시 [ ] 와일드카드사용하기 (0) | 2008.04.28 |