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

 

發表者：Jason

程式小雜工