SQL With（遞迴 CTE 查詢）

指定臨時命名的結果集，這些結果集稱為通用資料表運算式 (CTE)。該運算式源自簡單查詢，並且在單條 SELECT、INSERT、UPDATE 或 DELETE 語句的執行範圍內定義。該子句也可用在 CREATE VIEW 語句中，作為該語句的 SELECT 定義語句的一部分。通用資料表運算式可以包括對自身的引用。這種運算式稱為遞迴通用資料表運算式。

 Transact-SQL 文法約定

文法

  

[ 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 可以與基表或基視圖的名稱相同。在查詢中對 expression_name 的任何引用都會使用通用資料表運算式，而不使用基底物件。

column_name

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

CTE_query_definition

指定一個其結果集填充通用資料表運算式的 SELECT 語句。除了 CTE 不能定義另一個 CTE 以外，CTE_query_definition 的 SELECT 語句必須滿足與建立視圖時相同的要求。有關詳細資料，請參閱“備忘”部分和 CREATE VIEW (Transact-SQL)。

如果定義了多個 CTE_query_definition，則這些查詢定義必須用下列一個集合運算子聯結起來：UNION ALL、UNION、EXCEPT 或 INTERSECT。有關如何使用遞迴 CTE 查詢定義的詳細資料，請參閱下面的“備忘”部分和使用通用資料表運算式的遞迴查詢。

備忘

建立和使用 CTE 的指南

下列指南應用於非遞迴 CTE。有關適用於遞迴 CTE 的指南，請參閱後面的“定義和使用遞迴 CTE 的指南”。

• 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 中使用以下子句： 
  • COMPUTE 或 COMPUTE BY
  • ORDER BY（除非指定了 TOP 子句）
  • INTO 
  • 帶有查詢提示的 OPTION 子句
  • FOR XML
  • FOR BROWSE
• 如果將 CTE 用在屬於批處理的一部分的語句中，那麼在它之前的語句必須以分號結尾。
• 可以使用引用 CTE 的查詢來定義遊標。
• 可以在 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
  • HAVING
  • 標量彙總
  • TOP
  • LEFT、RIGHT、OUTER JOIN（允許出現 INNER JOIN）
  • 子查詢
  • 應用於對 CTE_query_definition 中的 CTE 的遞迴引用的提示。

下列指南適用於使用遞迴 CTE：

• 無論參與的 SELECT 語句返回的列的為空白性如何，遞迴 CTE 返回的全部列都可以為空白。
• 如果遞迴 CTE 組合不正確，可能會導致無限迴圈。例如，如果遞迴成員查詢定義對父列和子列返回相同的值，則會造成無限迴圈。可以使用 MAXRECURSION 提示以及在 INSERT、UPDATE、DELETE 或 SELECT 語句的 OPTION 子句中的一個 0 到 32,767 之間的值，來限制特定語句所允許的遞迴級數，以防止出現無限迴圈。這樣就能夠在解決產生迴圈的代碼問題之前控制語句的執行。伺服器範圍內的預設值是 100。如果指定 0，則沒有限制。每一個語句只能指定一個 MAXRECURSION 值。有關詳細資料，請參閱查詢提示 (Transact-SQL)。
• 不能使用包含遞迴通用資料表運算式的視圖來更新資料。
• 可以使用 CTE 在查詢上定義遊標。CTE 是定義遊標結果集的 select_statement 參數。遞迴 CTE 只允許使用快速順向資料指標和靜態（快照）遊標。如果在遞迴 CTE 中指定了其他遊標類型，則該類型將轉換為靜態資料指標類型。 
• 可以在 CTE 中引用遠程伺服器中的表。如果在 CTE 的遞迴成員中引用了遠程伺服器，那麼將為每個遠端資料表建立一個假離線，這樣就可以在本地反覆訪問這些表。

樣本

A. 建立一個簡單通用資料表運算式

以下樣本顯示直接向 Adventure Works Cycles 的每個經理報告的僱員的數目。

  

USE AdventureWorks;GOWITH DirReps(ManagerID, DirectReports) AS (    SELECT ManagerID, COUNT(*)     FROM HumanResources.Employee AS e    WHERE ManagerID IS NOT NULL    GROUP BY ManagerID)SELECT ManagerID, DirectReports FROM DirReps ORDER BY ManagerID;GO

B. 使用通用資料表運算式來限制次數和報告平均數

以下樣本顯示向經理報告的僱員的平均數。

  

WITH DirReps (Manager, DirectReports) AS (    SELECT ManagerID, COUNT(*) AS DirectReports    FROM HumanResources.Employee    GROUP BY ManagerID) SELECT AVG(DirectReports) AS [Average Number of Direct Reports]FROM DirReps WHERE DirectReports>= 2 ;GO

C. 多次引用同一個通用資料表運算式

以下樣本顯示 SalesOrderHeader 表中每個銷售人員的銷售訂單的總數和最近的銷售訂單的日期。CTE 在啟動並執行語句中被引用兩次：一次返回為銷售人員所選的列，另一次檢索銷售經理的類似詳細資料。銷售人員和銷售經理的資料都返回在一行中。

  

USE AdventureWorks;GOWITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)AS(    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)    FROM Sales.SalesOrderHeader    GROUP BY SalesPersonID)SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,    E.ManagerID, OM.NumberOfOrders, OM.MaxDateFROM HumanResources.Employee AS E    JOIN Sales_CTE AS OS    ON E.EmployeeID = OS.SalesPersonID    LEFT OUTER JOIN Sales_CTE AS OM    ON E.ManagerID = OM.SalesPersonIDORDER BY E.EmployeeID;GO

使用遞迴通用資料表運算式顯示遞迴的多個層級。

以下樣本顯示經理以及向經理報告的僱員的層次列表。

  

USE AdventureWorks;GOWITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS (    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel    FROM HumanResources.Employee    WHERE ManagerID IS NULL    UNION ALL    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1    FROM HumanResources.Employee e        INNER JOIN DirectReports d        ON e.ManagerID = d.EmployeeID )SELECT ManagerID, EmployeeID, EmployeeLevel FROM DirectReports ;GO

E. 使用遞迴通用資料表運算式顯示遞迴的兩個層級。

以下樣本顯示經理以及向經理報告的僱員。將返回的層級數目被限制為兩個。

  

USE AdventureWorks;GOWITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS (    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel    FROM HumanResources.Employee    WHERE ManagerID IS NULL    UNION ALL    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1    FROM HumanResources.Employee e        INNER JOIN DirectReports d        ON e.ManagerID = d.EmployeeID )SELECT ManagerID, EmployeeID, EmployeeLevel FROM DirectReports WHERE EmployeeLevel <= 2 ;GO

F. 使用遞迴通用資料表運算式顯示層次列表

以下樣本在樣本 C 的基礎上添加經理和僱員的名稱，以及他們各自的頭銜。通過縮排各個層級，反白經理和僱員的階層。

  

USE AdventureWorks;GOWITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),        e.Title,        e.EmployeeID,        1,        CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)    FROM HumanResources.Employee AS e    JOIN Person.Contact AS c ON e.ContactID = c.ContactID     WHERE e.ManagerID IS NULL    UNION ALL    SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +        c.FirstName + ' ' + c.LastName),        e.Title,        e.EmployeeID,        EmployeeLevel + 1,        CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +                  LastName)    FROM HumanResources.Employee as e    JOIN Person.Contact AS c ON e.ContactID = c.ContactID    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID    )SELECT EmployeeID, Name, Title, EmployeeLevelFROM DirectReports ORDER BY Sort;GO

G. 使用 MAXRECURSION 取消一條語句

可以使用 MAXRECURSION 來防止不合理的遞迴 CTE 進入無限迴圈。以下樣本特意建立了一個無限迴圈，然後使用 MAXRECURSION 提示將遞迴層級限制為兩級。

  

USE AdventureWorks;GO--Creates an infinite loopWITH cte (EmployeeID, ManagerID, Title) as(    SELECT EmployeeID, ManagerID, Title    FROM HumanResources.Employee    WHERE ManagerID IS NOT NULL  UNION ALL    SELECT cte.EmployeeID, cte.ManagerID, cte.Title    FROM cte     JOIN  HumanResources.Employee AS e         ON cte.ManagerID = e.EmployeeID)--Uses MAXRECURSION to limit the recursive levels to 2SELECT EmployeeID, ManagerID, TitleFROM cteOPTION (MAXRECURSION 2);GO

在更正代碼錯誤之後，就不再需要 MAXRECURSION。以下樣本顯示了更正後的代碼。

  

USE AdventureWorks;GOWITH cte (EmployeeID, ManagerID, Title)AS(    SELECT EmployeeID, ManagerID, Title    FROM HumanResources.Employee    WHERE ManagerID IS NOT NULL  UNION ALL    SELECT  e.EmployeeID, e.ManagerID, e.Title    FROM HumanResources.Employee AS e    JOIN cte ON e.ManagerID = cte.EmployeeID)SELECT EmployeeID, ManagerID, TitleFROM cte;GO

H. 使用通用資料表運算式來有選擇地執行 SELECT 語句中的遞迴操作

以下樣本顯示了為 ProductAssemblyID = 800 生產單車所需的產品裝配和組件階層。

  

USE AdventureWorks;GOWITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS(    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,        b.EndDate, 0 AS ComponentLevel    FROM Production.BillOfMaterials AS b    WHERE b.ProductAssemblyID = 800          AND b.EndDate IS NULL    UNION ALL    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,        bom.EndDate, ComponentLevel + 1    FROM Production.BillOfMaterials AS bom         INNER JOIN Parts AS p        ON bom.ProductAssemblyID = p.ComponentID        AND bom.EndDate IS NULL)SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,        ComponentLevel FROM Parts AS p    INNER JOIN Production.Product AS pr    ON p.ComponentID = pr.ProductIDORDER BY ComponentLevel, AssemblyID, ComponentID;GO

I. 在 UPDATE 語句中使用遞迴 CTE

以下樣本將直接或間接向 ManagerID 12 報告的所有僱員的 VacationHours 值增加 25%。通用資料表運算式將返回直接向 ManagerID 12 報告的僱員以及直接向這些僱員報告的僱員等的層次列表。只修改通用資料表運算式所返回的行。

  

USE AdventureWorks;GOWITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)AS(SELECT e.EmployeeID, e.VacationHours, 1  FROM HumanResources.Employee AS e  WHERE e.ManagerID = 12  UNION ALL  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1  FROM HumanResources.Employee as e  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID)UPDATE HumanResources.EmployeeSET VacationHours = VacationHours * 1.25FROM HumanResources.Employee AS eJOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;GO

使用多個錨點和遞迴成員

以下樣本使用多個錨點和遞迴成員來返回指定的人的所有祖先。建立了一個表，並在表中插入值，以建立由遞迴 CTE 返回的宗譜。

  

-- Genealogy tableIF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person;GOCREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);GOINSERT Person VALUES(1, 'Sue', NULL, NULL);INSERT Person VALUES(2, 'Ed', NULL, NULL);INSERT Person VALUES(3, 'Emma', 1, 2);INSERT Person VALUES(4, 'Jack', 1, 2);INSERT Person VALUES(5, 'Jane', NULL, NULL);INSERT Person VALUES(6, 'Bonnie', 5, 4);INSERT Person VALUES(7, 'Bill', 5, 4);GO-- Create the recursive CTE to find all of Bonnie's ancestors.WITH Generation (ID) AS(-- First anchor member returns Bonnie's mother.    SELECT Mother     FROM Person    WHERE Name = 'Bonnie'UNION-- Second anchor member returns Bonnie's father.    SELECT Father     FROM Person    WHERE Name = 'Bonnie'UNION ALL-- First recursive member returns male ancestors of the previous generation.    SELECT Person.Father    FROM Generation, Person    WHERE Generation.ID=Person.IDUNION ALL-- Second recursive member returns female ancestors of the previous generation.    SELECT Person.Mother    FROM Generation, Person    WHERE Generation.ID=Person.ID)SELECT Person.ID, Person.Name, Person.Mother, Person.FatherFROM Generation, PersonWHERE Generation.ID = Person.ID;GO

 詳情參見http://msdn.microsoft.com/zh-cn/library/ms175972(v=SQL.90).aspx

一個資料表（t_tree）：表中的資料有三個欄位：id、node_name、parent_id。實際上，這個表中儲存了一個樹型結構，分三層：省、市、區。其中id表示當前省、市或區的id號、node_name表示名稱、parent_id表示節點的父節點的id。
　　現在有一個需求，要查詢出某個省下面的所有市和區（查詢結果包含省）。如果只使用SQL語句來實現，需要使用到遊標、暫存資料表等技術。但在SQL Server2005中還可以使用CTE來實現。
　　從這個需求來看屬於遞迴調用，也就是說先查出滿足調價的省的記錄，在本例子中的要查“遼寧省”的記錄，如下：
　　id　 node_name　 parent_id
　　1　　 遼寧省　　0
　　然後再查所有parent_id欄位值為1的記錄，如下：
　　id　 node_name　 parent_id
　　2　　瀋陽市　　 1
　　3　　大連市　　 1
　　最後再查parent_id欄位值為2或3的記錄，如下：
　　id　　node_name　　parent_id
　　4　　 大東區　　2
　　5　　 瀋河區　　2
　　6　　 鐵西區　　2
　　將上面三個結果集合并起來就是最終結果集。
　　上述的查詢過程也可以按遞迴的過程進行理解，即先查指定的省的記錄（遼寧省），得到這條記錄後，就有了相應的id值，然後就進入了的遞迴過程，如所示。
　　

從上面可以看出，遞迴的過程就是使用union all合并查詢結果集的過程，也就是相當於下面的遞迴公式：
　　resultset(n) = resultset(n-1)union allcurrent_resultset
　　其中resultset(n)表示最終的結果集，resultset(n - 1)表示倒數第二個結果集，current_resultset表示當前查出來的結果集，而最開始查詢出“遼寧省”的記錄集相當於遞迴的初始條件。而遞迴的結束條件是current_resultset為空白。下面是這個遞迴過程的虛擬碼：
　　publicresultsetgetResultSet(resultset)
　　{
　　if(resultsetisnull)
　　{
　　current_resultset=第一個結果集（包含省的記錄集）
　　將結果集的id儲存在集合中
　　getResultSet(current_resultset)
　　}
　　current_resultset=根據id集合中的id值查出當前結果集
　　if(current_resultisnull)returnresultset
　　將當前結果集的id儲存在集合中
　　return　getResultSet(resultsetunionallcurrent_resultset)
　　}
　　//獲得最終結果集
　　resultset=getResultSet(null)
　　從上面的過程可以看出，這一遞迴過程實現起來比較複雜，然而CTE為我們提供了簡單的文法來簡化這一過程。
　　實現遞迴的CTE文法如下：
　　[WITH[,n]]
　　::=
　　expression_name[(column_name[,n])]
　　AS(
　　CTE_query_definition1　--　錨點成員（也就是初始值或第一個結果集）
　　unionall
　　CTE_query_definition2　--　遞迴成員
　　)
　　下面是使用遞迴CTE來獲得“遼寧省”及下面所有市、區的資訊的SQL語句：
　　with
　　districtas
　　(
　　--　獲得第一個結果集，並更新最終結果集
　　select*fromt_treewherenode_name=N’遼寧省’
　　unionall
　　--　下面的select語句首先會根據從上一個查詢結果集中獲得的id值來查詢parent_id
　　--　欄位的值，然後district就會變當前的查詢結果集，並繼續執行下面的select語句
　　--　如果結果集不為null，則與最終的查詢結果合并，同時用合并的結果更新最終的查
　　--　詢結果；否則停止執行。最後district的結果集就是最終結果集。
　　selecta.*fromt_treea,districtb
　　wherea.parent_id=b.id
　　)
　　select*fromdistrict
　　查詢後的結果如所示。

　　下面的CTE查詢了非葉子節點：
　　with
　　districtas
　　(
　　select*fromt_treewherenode_name=N’遼寧省’
　　unionall
　　selecta.*fromt_treea,districtb
　　wherea.parent_id=b.id
　　),
　　district1as
　　(
　　selecta.*fromdistrictawherea.idin(selectparent_idfromdistrict)　　)
　　select*fromdistrict1
　　查詢結果如所示。

　　註：只有“遼寧省”和“瀋陽市”有下子節點。
　　在定義和使用遞迴CTE時應注意如下幾點：
　　1.遞迴 CTE 定義至少必須包含兩個 CTE 查詢定義，一個錨點成員和一個遞迴成員。可以定義多個錨點成員和遞迴成員；但必須將所有錨點成員查詢定義置於第一個遞迴成員定義之前。所有 CTE 查詢定義都是錨點成員，但它們引用 CTE 本身時除外。
　　2.錨點成員必須與以下集合運算子之一結合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。在最後一個錨點成員和第一個遞迴成員之間，以及組合多個遞迴成員時，只能使用 UNION ALL 集合運算子。
　　3.錨點成員和遞迴成員中的列數必須一致。
　　4.遞迴成員中列的資料類型必須與錨點成員中相應列的資料類型一致。
　　5.遞迴成員的 FROM 子句只能引用一次 CTE expression_name。
　　6.在遞迴成員的 CTE_query_definition 中不允許出現下列項：
　　（1）SELECT DISTINCT
　　（2）GROUP BY
　　（3）HAVING
　　（4）標量彙總
　　（5）TOP
　　（6）LEFT、RIGHT、OUTER JOIN（允許出現 INNER JOIN）
　　（7）子查詢
　　（8）應用於對 CTE_query_definition 中的 CTE 的遞迴引用的提示。
　　7.無論參與的 SELECT 語句返回的列的為空白性如何，遞迴 CTE 返回的全部列都可以為空白。
　　8.如果遞迴 CTE 組合不正確，可能會導致無限迴圈。例如，如果遞迴成員查詢定義對父列和子列返回相同的值，則會造成無限迴圈。可以使用 MAXRECURSION 提示以及在 INSERT、UPDATE、DELETE 或 SELECT 語句的 OPTION 子句中的一個 0 到 32,767 之間的值，來限制特定語句所允許的遞迴級數，以防止出現無限迴圈。這樣就能夠在解決產生迴圈的代碼問題之前控制語句的執行。伺服器範圍內的預設值是 100。如果指定 0，則沒有限制。每一個語句只能指定一個 MAXRECURSION 值。
　　9.不能使用包含遞迴通用資料表運算式的視圖來更新資料。
　　10.可以使用 CTE 在查詢上定義遊標。遞迴 CTE 只允許使用快速順向資料指標和靜態（快照）遊標。如果在遞迴 CTE 中指定了其他遊標類型，則該類型將轉換為靜態資料指標類型。
　　11.可以在 CTE 中引用遠程伺服器中的表。如果在 CTE 的遞迴成員中引用了遠程伺服器，那麼將為每個遠端資料表建立一個假離線，這樣就可以在本地反覆訪問這些表。

來源:考試大-電腦三級考試