摘要:SQL - 使用 一般資料表運算式 CTE (Common Table Expression)
早期可以透過暫存資料表(# 或 ##)或者 table 來存放資料，現在也可以使用 一般資料表運算式 CTE (Common Table Expression) 來暫存資料。雖然 CTE 的查詢是使用 Select 查詢，但是依照 MSDN 的說明，仍有一些限制，那就是在 CTE 中不可以使用以下的子句：
1.COMPUTE 或 COMPUTE BY
2.ORDER BY (除非指定了 TOP 子句)
3.INTO
4.含有查詢提示的 OPTION 子句
5.FOR XML
6.FOR BROWSE
以下就來實作一些 CTE 的範例...
範例一：單一的 CTE 用法
WITH OrdersTable (訂單ID, 客戶ID, 員工ID) as
(
Select OrderID, CustomerID, EmployeeID
from dbo.Orders
)
Select *
From OrdersTable
範例二：兩個 CTE 作 Join
WITH OrdersTable (訂單ID, 客戶ID, 員工ID) as
(
Select OrderID, CustomerID, EmployeeID
from dbo.Orders
),
OrderDetailsTable as
(
Select OrderID as '訂單ID', ProductID as '產品ID', UnitPrice as '價格'
from dbo.[Order Details]
)
Select *
From OrdersTable A inner join OrderDetailsTable B
on A.訂單ID = B.訂單ID 
範例三：CTE 遞迴 (參考：黑暗大的部落格文章)
資料清單
Code：
--如果CTE的WITH不在第一列, 前方要加上;
;WITH 公司組織結果(部門名稱, 直屬部門, 層級, 排序欄位)
AS
(
--Recursive CTE分為兩個部分, 第一部分為Anchor Member
--指不會被遞迴呼叫到的部分
SELECT 部門名稱, 直屬部門, 0, CONVERT(nvarchar(128), 部門ID)
FROM dbo.公司組織 WHERE 直屬部門=N'ROOT'
UNION ALL
--UNION ALL後方的部分稱為Recursive Member, 會在遞迴過程中反覆執行,
--直到無任何查詢結果為止
SELECT P.部門名稱, P.直屬部門, B.層級+1,
CONVERT(nvarchar(128), B.排序欄位 + '-' + CONVERT(nvarchar(128), P.部門ID))
FROM dbo.公司組織 P, 公司組織結果 B
WHERE P.直屬部門=B.部門名稱
)
SELECT (REPLICATE(' ', 層級) + 部門名稱) as '部門名稱', 層級, 排序欄位
FROM 公司組織結果
ORDER BY 排序欄位
另一個 CTE 遞迴範例 (參考：Journey to SQL Authority with Pinal Dave)
Code：
USE AdventureWorks
GO
WITH Emp_CTE AS (
--找出 ManagerID 為 NULL 的資料當作 Anchor Member
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
--之後以 Anchor Member 為依據遞迴查詢
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE
GO 
參考：
SQL 2005 T-SQL Enhancement: Common Table Expression
SQL SERVER – Simple Example of Recursive CTE
WITH common_table_expression (Transact-SQL)
利用CTE將分頁資料取出-Stored Procedure篇