SQLServer中的CTE(Common Table Expression)通用表表達式使用詳解

2022 年 1 月 4 日
筆記
SQLServer, 實用資料庫技術, 開發技術, 資料庫優化, 資料庫開發應用, 資料庫開發相關技術

概述

我們經常會編寫由基本的 SELECT/FROM/WHERE 類型的語句派生而來的複雜 SQL 語句。其中一種方案是需要編寫在 FROM 子句內使用派生表（也稱為內聯視圖）的 Transact-SQL (T-SQL) 查詢來使開發人員能獲取一個結果集，並立即將該結果集加入到 SELECT 語句中的其他表、視圖和用戶定義函數中。另一種方案是使用視圖而不是派生表。這兩種方案都有其各自的優勢和劣勢。

當使用 SQL Server 2005 +時，我更傾向於第三種方案，就是使用通用表表達式 (CTE)。CTE 能改善程式碼的可讀性（以及可維護性），且不會有損其性能。此外，與早期版本的 SQL Server 相比，它們使得用 T-SQL 編寫遞歸程式碼簡單了許多。

本文將介紹 CTE 的工作原理以及可用它們來應對的情況。接著將討論使用 CTE 相對於使用傳統的 T-SQL 構造的優勢，如派生表、視圖和自定義過程。通過事例解釋它們的使用方法和適用情況。還將演示 CTE 是如何處理遞歸邏輯並定義遞歸 CTE 的運行方式的。本文使用 SQL Server2014附帶的 Northwind 和 AdventureWorks 樣例資料庫。

視圖、派生表和 CTE

如果查詢需要在一組數據中進行選取，而這些數據在資料庫中並不是以表的形式存在，則 CTE 可能非常有用。例如，您可能想要編寫一個針對一組聚合數據的查詢，該聚合數據基於客戶及其訂單來計算值。這些聚合數據可能會將 Customers、Orders 和 Order Details 表聯接在一起，以計算訂單的總和以及平均值。此外，您可能想要查詢聚合的行集。一個方法是創建一個視圖，首先收集聚合數據，然後針對該視圖編寫一個查詢。另一個方法是使用派生錶針對聚合數據編寫一個查詢通過將 SQL 語句移到 FROM 子句中並對其進行查詢，可實現這一點。

視圖通常用來分解大型的查詢，以便用更易讀的方式來查詢它們。例如，一個視圖可以表示一個 SELECT 語句，該語句會將 10 個表聯接起來，選擇許多列，然後根據涉及的一組邏輯來過濾行。接著，可以通過其他 SELECT 語句在整個資料庫中查詢該視圖。此抽象使由該視圖表徵的行集更容易訪問，而且無需在臨時表中複製或存儲數據。

假定許可權許可，這個視圖還能在整個資料庫中被重複使用。例如，在Figure 1 中，已經創建了一個視圖，並為另一個 T-SQL 語句所使用。然而，當您想要收集數據並且只使用一次的時候，視圖未必是最佳解決方案。由於視圖是存在於資料庫中、適用於所有批處理的資料庫對象，那麼創建僅用於單個 T-SQL 批處理的視圖就有些多餘。

Figure 1 被查詢的視圖

CREATE VIEW vwMyView AS
SELECT
    EmployeeID, COUNT(*) AS NumOrders,  MAX(OrderDate) AS MaxDate
FROM Orders
GROUP BY EmployeeID
GO

SELECT
    e.EmployeeID, oe.NumOrders, oe.MaxDate, e.ReportsTo AS ManagerID,
    om.NumOrders, om.MaxDate
FROM
    Employees AS e
    INNER JOIN vwMyView AS oe ON e.EmployeeID = oe.EmployeeID
    INNER JOIN vwMyView AS om ON e.ReportsTo = om.EmployeeID

另一種方法是創建派生表（也稱為內聯視圖）。要創建派生表，在由括弧包圍的 FROM 子句中移動 SELECT 語句即可。接著就能像表或視圖一樣查詢或者聯接它。

Figure 2 中的程式碼解決的查詢與Figure 1 所解決的相同，但使用的是派生表而不是視圖。儘管只能在派生表所在的語句中訪問它們，但是，表通常使查詢變得更難以閱讀和維護。如果想要在同一個批處理中多次使用派生表，此問題會變得更加嚴重，因為隨後必須複製和粘貼派生表才能重複使用它。

Figure 2 使用派生表的查詢

SELECT
    e.EmployeeID, oe.NumOrders, oe.MaxDate,  e.ReportsTo AS ManagerID,
    om.NumOrders, om.MaxDate
FROM
    Employees AS e
    INNER JOIN
        (SELECT EmployeeID, COUNT(*), MAX(OrderDate)
         FROM Orders
         GROUP BY EmployeeID) AS oe(EmployeeID, NumOrders, MaxDate)
        ON e.EmployeeID = oe.EmployeeID
    LEFT JOIN
        (SELECT EmployeeID, COUNT(*), MAX(OrderDate)
         FROM Orders
         GROUP BY EmployeeID) AS om(EmployeeID, NumOrders, MaxDate)
        ON e.ReportsTo = om.EmployeeID

CTE 非常適用於此類情形，它不僅提升了 T-SQL 的可讀性（就像視圖一樣），而且能在同一個批處理後緊跟的查詢中多次使用。當然，超出該範圍它就不適用了。另外，CTE 是語言級別的構造， SQL Server 不會在內部創建臨時表或虛擬表。每次在緊隨其後的查詢中引用 CTE 的底層查詢時都會調用它。

因此，同樣的情形也能用 CTE 來編寫，如Figure 3 所示。EmpOrdersCTE 收集聚合數據，然後在緊隨 CTE 之後的查詢中使用該數據。使用 CTE 之後，Figure 3 中的程式碼令查詢變得非常易讀（就像視圖一樣），而且並沒有創建系統對象來存儲元數據。

Figure 3 使用 CTE 查詢

;WITH EmpOrdersCTE (EmployeeID, NumOrders, MaxDate) AS
(
  SELECT EmployeeID, COUNT(*), MAX(OrderDate)
  FROM Orders
  GROUP BY EmployeeID
)

SELECT
    e.EmployeeID,  oe.NumOrders, oe.MaxDate,
    e.ReportsTo AS ManagerID,  om.NumOrders, om.MaxDate
FROM
    Employees AS e
    INNER JOIN EmpOrdersCTE oe ON e.EmployeeID = oe.EmployeeID
    LEFT JOIN EmpOrdersCTE om ON e.ReportsTo = om.EmployeeID

CTE 的結構

CTE語法

[ WITH <common_table_expression> [ ,...n ] ]  
<common_table_expression>::=  
    expression_name [ ( column_name [ ,...n ] ) ]  
    AS  
    ( CTE_query_definition )

參數

expression_name
是公用表表達式的有效標識符。 expression_name 須不同於在同一 WITH <common_table_expression> 子句中定義的任何其他公用表表達式的名稱，但可以與基表或基視圖的名稱相同。在查詢中對 expression_name 的任何引用都會使用公用表表達式，而不使用基對象。

column_name
在公用表表達式中指定列名。在一個 CTE 定義中不允許出現重複的名稱。指定的列名數必須與 CTE_query_definition 結果集中列數相匹配。只有在查詢定義中為所有結果列都提供了不同的名稱時，列名列表才是可選的。

CTE_query_definition
指定一個其結果集填充公用表表達式的 SELECT 語句。除了 CTE 不能定義另一個 CTE 以外，CTE_query_definition 的 SELECT 語句必須滿足與創建視圖相同的要求。

如果定義了多個 CTE_query_definition，則這些查詢定義必須用下列一個集合運算符聯接起來：UNION ALL、UNION、EXCEPT 或 INTERSECT。

現在我將用一個簡單的 CTE 為例來演示如何構造 CTE。CTE 以 WITH 關鍵字開始。然而，如果 CTE 不是批處理中的第一個語句，則必須在 WITH 關鍵字前添加一個分號。作為最佳做法，我傾向於在所有的 CTE 之前都加上一個分號作為前綴，我發現這種一致的方式比起必須牢記是否需要添加分號來，要容易得多。

WITH 關鍵字後面是 CTE 的名稱，接著是一個列別名的可選列表。列別名對應於 CTE 內的 SELECT 語句返回的列。可選列別名的後面是 AS 關鍵字，這是必需的。AS 關鍵字後面是用括弧括起來、定義 CTE 的查詢表達式。

請看這個示例：

;WITH myCTE (CustID, Co) AS
(
  SELECT CustomerID, CompanyName FROM Customers
)
SELECT CustID, Co FROM myCTE

CustomerID 和 CompanyName 列的別名為 CustID 和 Co。接著跟隨 CTE 其後的是通過列別名引用 CTE 的 SELECT 語句。

理解 CTE

在設計 CTE 之前，必須理解它的工作原理和遵循的規則。本文介紹了 CTE 的適用情況，以及在 CTE 內什麼是可以使用的，什麼是不可以使用的。對於初學者來說，可以在 T-SQL 批處理、用戶自定義函數、存儲過程、觸發器或視圖中創建並使用 CTE。

CTE 僅能被緊隨其後的語句所引用。這意味著如果要使用 CTE，則必須緊隨 T-SQL 批處理中的 CTE 之後編寫引用 CTE 的查詢。例如，以下批處理會產生錯誤：

;WITH myCTE (CustID, Co) AS
(
  SELECT CustomerID, CompanyName FROM Customers
)
SELECT CompanyName FROM Customers WHERE CustomerID = 'ALFKI'
SELECT CustID, Co FROM myCTE

在本段程式碼中，myCTE 僅供緊隨其後的第一個查詢使用。當第二個查詢引用 myCTE 時，CTE 不在範圍之內，並且引發異常（對象名「myCTE」無效）。

另請注意，因為 CTE 預期由另一個可能隨之肯定要重新處理數據的查詢引用，所以 CTE 的查詢不能含有 ORDER 和 COMPUTE 之類的語句。然而，諸如 FOR XML 的複雜語句仍可用來定義和運行 CTE。例如，您可以使用 FOR XML 子句來查詢 CTE 並返回其結果，如下所示。

;WITH myCTE AS
(
  SELECT c.CustomerID, c.CompanyName, o.OrderID, o.OrderDate
  FROM Customers c
  INNER JOIN Orders o ON c.CustomerID = o.CustomerID
)

SELECT CustomerID, CompanyName, OrderID, OrderDate
FROM myCTE FOR XML AUTO

一旦定義了 CTE，跟隨其後的首個查詢便能多次引用它。這一功能在某個查詢需要多次引用 CTE 時尤為有用。Figure 3 中的程式碼示例演示了查詢如何引用 EmpOrdersCTE 兩次，以便能獲取員工和主管的資訊。當需要多次引用同一行集時，這非常有用；引用 CTE 兩次比複製該查詢要簡單得多。

CTE 並不一定由 SELECT 語句使用；任何引用 CTE 所生成行集的語句都可使用它。這意味著 CTE 後面可以跟隨使用 CTE 的 SELECT、INSERT、UPDATE 或 DELETE 語句。您也可以在使用 CTE 的查詢中使用只進和快照游標。

此外，CTE 後面也可以跟隨另一個 CTE。在想要把中間結果聚集到行集時，可使用這種技術從其他 CTE 構建 CTE。當創建從其他 CTE 構建的 CTE 時，請用逗號分隔 CTE 的定義。

Figure 4 所示的示例定義了 EmpOrdersCTE，它收集了一個員工列表和每個員工的訂單總數。第二個 CTE 的名稱是 MinMaxOrdersCTE，它查詢第一個 EmpOrdersCTE 並在行集上執行聚合函數來確定員工訂單的平均數、最小數和最大數。

Figure 4 引用另一個 CTE 的 CTE

;WITH 
EmpOrdersCTE (EmployeeID, NumOrders)
AS
(
  SELECT EmployeeID, COUNT(*)
  FROM Orders
  GROUP BY EmployeeID
),
MinMaxOrdersCTE (Mn, Mx, Diff)
AS
(
  SELECT MIN(NumOrders), MAX(NumOrders), AVG(NumOrders)
  FROM EmpOrdersCTE
)
SELECT Mn, Mx, Diff
FROM MinMaxOrdersCTE

通過用逗號來分隔，在 WITH 關鍵字後面可以定義多個 CTE。每個 CTE 都可以被緊隨其後的 CTE 所引用，形成層接的構建關係。CTE 定義後面的數據操作語言 (DML) 語句也可引用 WITH 子句中定義的任何 CTE。

遞歸規則

CTE 還可用於實現遞歸演算法。在需要編寫調用其本身的演算法時，遞歸邏輯很有用——這通常用來遍歷一組嵌套的數據。編寫遞歸邏輯可能很複雜，特別是使用 T-SQL 之類的語言的時候。然而，這正是 CTE 旨在解決的特別問題之一。創建遞歸 CTE 的基本公式如下所示：

創建一個返回頂層（這是定位點成員）的查詢。
編寫一個遞歸查詢（這是遞歸成員）。
通過 UNION 將第一個查詢與遞歸查詢結合起來。
確保存在沒有行會被返回的情況（這是終止檢查）。

您的遞歸 CTE 如下所示：

;WITH myRecursiveCTE(col1, col2, ... coln) AS
( 
  -- 定位點成員 查詢
  UNION ALL
  -- 遞歸成員 查詢來自CTE的自身數據
)

　　當編寫不涉及 CTE 的自定義遞歸過程時，必須包含一個顯式終止子句。這個終止子句負責確保遞歸演算法最後將終止，並彈出了遞歸調用堆棧。若無此子句，您的程式碼最終將無限循環下去。

　　CTE 可從兩個方面幫助處理終止子句。首先是一個隱式終止子句，當遞歸成員返回零記錄時出現。此時，遞歸成員查詢不會遞歸調用 CTE，取而代之的彈出了遞歸調用堆棧。其次是能顯式設置 MAXRECURSION 層。

MAXRECURSION 層可以在含有 CTE 的批處理中或通過伺服器端設置（伺服器範圍的設置默認值為 100，除非您更改它）顯式設置。這個設置限制了 CTE 可遞歸調用其本身的次數。如果達到限制次數，則將引發異常。設置 MAXRECURSION 層的語法是在 SELECT 語句中的 CTE 後面使用 OPTION 子句，如下所示：

-- DEFINE YOUR CTE HERE
SELECT * FROM EmpCTE
OPTION (MAXRECURSION 7)

在設計遞歸 CTE 時，還有一些其他規則也得銘記在心。遞歸 CTE 必須包含定位點成員和遞歸成員。這兩種成員必須擁有相同數量的列，而且同屬於這兩種成員的列必須具有匹配的數據類型。遞歸成員只能引用 CTE 一次，並且成員不能使用下列子句或關鍵字：

SELECT DISTINCT
GROUP BY
HAVING
TOP
LEFT/RIGHT OUTER JOIN

遞歸偏移

在數據和行集方面，遞歸用於解決您需要在不同情況下針對同一組數據重複執行同一邏輯時所遇到的問題。例如，假設您需要找出所有銷售人員以及他們的上級，然後以分層順序返回數據。Figure 5 演示了一個使用 CTE 的解決方案，該方案通過遞歸來收集銷售副總裁下屬的員工的列表。

Figure 5 遞歸收集銷售人員

;WITH EmpCTE(EmployeeID, EmployeeFirstName,EmployeeLastName, MgrID, SalesLevel)
AS
(
  -- 定位點成員
  SELECT EmployeeID, FirstName, LastName, ReportsTo, 0
  FROM Employees
  WHERE EmployeeID = 2 -- Start with the VP of Sales

  UNION ALL

  -- 遞歸成員
  SELECT
    e.EmployeeID, e.FirstName, e.LastName, e.ReportsTo, m.SalesLevel+1
  FROM
    Employees AS e
    INNER JOIN EmpCTE m ON e.ReportsTo = m.EmployeeID
)

 -- 使用CTE
SELECT EmployeeID, EmployeeFirstName, EmployeeLastName,
       MgrID, SalesLevel
FROM EmpCTE

除了少數其他方面，Figure 5 中所示的遞歸 CTE 看起來很像標準 CTE。其中標準 CTE 包含一個定義行集的查詢，遞歸 CTE 定義兩個查詢定義。第一個查詢定義（即定位點成員）定義了將在調用 CTE 時執行的查詢。第二個查詢定義，即遞歸成員，定義了一個返回與定位點成員相同的列和數據類型的查詢。遞歸成員還檢索接下來將被用於遞歸回調到 CTE 的值。查詢的結果通過 UNION 語句結合在一起。

Figure 5 中的 EmpCTE 顯示了收集銷售副總裁的員工記錄的定位點成員 (EmployeeID = 2)。定位點成員查詢的最後一列返回 0 值，這表示分層順序的第 0 層，也就是最頂層。遞歸成員查詢收集向其各自上級彙報的員工的列表。這通過聯接 Employees 表和 EmpCTE 來實現。

從遞歸成員中可以檢索相同的列，但是 SalesLevel 列的計算方式是：取當前員工的主管，收集主管的 SalesLevel，然後在其基礎上增加 1。表達式 m.SalesLevel+1 為所有直接向銷售副總裁彙報的員工（從定位點成員中檢索到）的 SalesLevel 賦值 1。然後，所有向那些員工彙報的員工的 SalesLevel 值變為 2。對隨後銷售機構層次結構中的每一後續層次，其 SalesLevel 都以此方式遞增。

補充：

目前僅有Sql Server 2005、2008、2012+、MySQL8.0+、Oracle11g、IBM DB2和PostegreSQL8.4支援CTE；SQLite和Infomix暫時不支援。

非遞歸公用表表達式適用準則

CTE 之後必須跟隨引用部分或全部 CTE 列的單條 SELECT、INSERT、UPDATE 或 DELETE 語句。也可以在 CREATE VIEW 語句中將 CTE 指定為視圖中 SELECT 定義語句的一部分。
可以在非遞歸 CTE 中定義多個 CTE 查詢定義。定義必須與以下集合運算符之一結合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。
CTE 可以引用自身，也可以引用在同一 WITH 子句中預先定義的 CTE。不允許前向引用。
不允許在一個 CTE 中指定多個 WITH 子句。例如，如果 CTE_query_definition 包含一個子查詢，則該子查詢不能包括定義另一個 CTE 的嵌套 WITH 子句。
不能在 CTE_query_definition 中使用以下子句：
- ORDER BY（除非指定了 TOP 子句）
- INTO
- 帶有查詢提示的 OPTION 子句
- FOR BROWSE
如果將 CTE 用在屬於批處理的一部分的語句中，那麼在它之前的語句必須以分號結尾。
可以使用引用 CTE 的查詢來定義游標。
可以在 CTE 中引用遠程伺服器中的表。
在執行 CTE 時，任何引用 CTE 的提示都可能與該 CTE 訪問其基礎表時發現的其他提示相衝突，這種衝突與引用查詢中的視圖的提示所發生的衝突相同。發生這種情況時，查詢將返回錯誤。

遞歸公用表表達式適用準則

遞歸 CTE 定義至少必須包含兩個 CTE 查詢定義，一個定位點成員和一個遞歸成員。可以定義多個定位點成員和遞歸成員；但必須將所有定位點成員查詢定義置於第一個遞歸成員定義之前。所有 CTE 查詢定義都是定位點成員，但它們引用 CTE 本身時除外。
定位點成員必須與以下集合運算符之一結合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。在最後一個定位點成員和第一個遞歸成員之間，以及組合多個遞歸成員時，只能使用 UNION ALL 集合運算符。
定位點成員和遞歸成員中的列數必須一致。
遞歸成員中列的數據類型必須與定位點成員中相應列的數據類型一致。
遞歸成員的 FROM 子句只能引用一次 CTE expression_name。
在遞歸成員的 CTE_query_definition 中不能出現下列項：
- SELECT DISTINCT
- GROUP BY
- PIVOT（當資料庫兼容性級別為 110 或更高級別時。請參閱 SQL Server 2016 中資料庫引擎功能的重大更改。）
- HAVING
- 標量聚合
- TOP
- LEFT、RIGHT、OUTER JOIN（允許使用 INNER JOIN）
- 子查詢
- 應用於 CTE_query_definition 中 CTE 的遞歸引用的提示。

結束語

比起那些在查詢中使用複雜的派生表或引用那些在 T-SQL 批處理外部定義的視圖的方案，CTE 使得編寫 T-SQL 更具可讀性。此外，CTE 還為解決使用遞歸演算法的過程中遇到的難題提供了一個更先進的工具。不管您使用的是非遞歸 CTE 還是遞歸 CTE，您都會發現 CTE 可以幫您應對許多常見開發情況，並且可在不損害性能的情況下提升可讀性。

一路走來數個年頭，感謝RDIFramework.NET框架的支援者與使用者，大家可以通過下面的地址了解詳情。

RDIFramework.NET官方網站：//www.rdiframework.net/

RDIFramework.NET官方部落格：//blog.rdiframework.net/

特別說明，框架相關的技術文章請以官方網站為準，歡迎大家收藏！

RDIFramework.NET框架由海南國思軟體科技有限公司專業團隊長期打造、一直在更新、一直在升級，請放心使用！

歡迎關注RDIFramework.NET框架官方微信公眾號（微訊號：guosisoft），及時了解最新動態。

使用微信掃描二維碼立即關注

Tags: SQLServer 實用資料庫技術開發技術資料庫優化資料庫開發應用資料庫開發相關技術