以一般資料表運算式(Common Table Expression)分解SQL

1.一般資料表運算式可作為暫存表

一個包含許多子查詢的複雜查詢，邏輯上相當難以分析，若先分解成數個邏輯較簡單的暫存資料表再行串接出結果查詢式，則較易分析，但是其缺點是無法以一個查詢表示；而一般資料表運算式(CTE, Common Table Expression)可對查詢結果作命名，等價於暫存資料表之效果，又兼具分解子查詢的效果。

範例1：單一的 CTE 用法

WITH OrdersTable (訂單ID, 客戶ID, 員工ID) as
(
Select OrderID, CustomerID, EmployeeID
from dbo.Orders
)
Select *
From OrdersTable

範例二：2兩個 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