使用一般資料表運算式的遞迴查詢

發行項
12/15/2008

一般資料表運算式 (CTE) 能夠自我參考，進而建立遞迴 CTE，這是一大優點。遞迴 CTE 會重複執行初始 CTE 以傳回資料子集，直到取得完整的結果集為止。

在 SQL Server 2005 中，參考遞迴 CTE 的查詢就是遞迴查詢。傳回階層式資料是一種常用的遞迴查詢，例如：在公司組織圖中顯示員工，或顯示用料表中的資料，在此種用料表中，上層產品包含了一或多項元件，而這些元件可能又包含了子元件或者是其他上層產品的元件。

遞迴 CTE 可大幅簡化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 陳述式中執行遞迴查詢所需的程式碼。在舊版的 SQL Server 中，遞迴查詢通常需要使用暫存資料表、資料指標與邏輯來控制遞迴步驟的流程。如需一般資料表運算式的詳細資訊，請參閱＜使用一般資料表運算式＞。

遞迴 CTE 的結構

Transact-SQL 中的遞迴 CTE 結構與其他程式語言中的遞迴常式類似。雖然其他語言中的遞迴常式會傳回純量值，但遞迴 CTE 可傳回多個資料列。

遞迴 CTE 包含三個元素：

常式的引動過程。
遞迴 CTE 的第一個引動過程包含由 UNION ALL、UNION、EXCEPT 或 INTERSECT 運算子聯結的一或多個 CTE_query_definitions。這些查詢定義會形成 CTE 結構的基底結果集，因此稱為錨點成員。
CTE_query_definitions 若未參考 CTE 本身，即會被視為錨點成員。所有錨點成員查詢定義都必須位於第一個遞迴成員定義之前，且必須使用 UNION ALL 運算子來聯結最後一個錨點成員與第一個遞迴成員。
常式的遞迴引動過程。
遞迴引動過程包含了由參考 CTE 本身之 UNION ALL 運算子所聯結的一或多個 CTE_query_definitions。這些查詢定義稱為遞迴成員。
終止檢查。
終止檢查是隱含的；當先前的引動過程不傳回資料列時，遞迴就會停止。

附註：
撰寫錯誤的遞迴 CTE 可能會造成無限迴圈。例如，若遞迴成員查詢定義對父資料行與子資料行傳回相同的值，就會建立無限迴圈。在測試遞迴查詢的結果時，您可以限制特定陳述式所允許的遞迴層級數，其做法便是在 INSERT、UPDATE、DELETE 或 SELECT 陳述式的 OPTION 子句中，使用 MAXRECURSION 提示以及 0 到 32,767 之間的值。如需詳細資訊，請參閱＜查詢提示 (Transact-SQL)＞與＜WITH common_table_expression (Transact-SQL)＞。

撰寫錯誤的遞迴 CTE 可能會造成無限迴圈。例如，若遞迴成員查詢定義對父資料行與子資料行傳回相同的值，就會建立無限迴圈。在測試遞迴查詢的結果時，您可以限制特定陳述式所允許的遞迴層級數，其做法便是在 INSERT、UPDATE、DELETE 或 SELECT 陳述式的 OPTION 子句中，使用 MAXRECURSION 提示以及 0 到 32,767 之間的值。如需詳細資訊，請參閱＜查詢提示 (Transact-SQL)＞與＜WITH common_table_expression (Transact-SQL)＞。

虛擬程式碼與語意

遞迴 CTE 結構至少必須包含一個錨點成員與一個遞迴成員。下列虛擬程式碼顯示含有單一錨點成員與單一遞迴成員的簡單遞迴 CTE 所具備的元件。

WITH cte_name ( column_name [,...n] )

AS

(

CTE_query_definition –- Anchor member is defined.

UNION ALL

CTE_query_definition –- Recursive member is defined referencing cte_name.

)

-- Statement using the CTE

SELECT *

FROM cte_name

遞迴執行的語意如下：

將 CTE 運算式分割為錨點成員與遞迴成員。
執行錨點成員以建立第一個引動過程或基底結果集 (T0)。
執行遞迴成員，以 Ti 做為輸入，而以 Ti+1 做為輸出。
重複步驟 3，直到傳回空的結果集為止。
傳回結果集。這是 T0 至 Tn 的 UNION ALL。

範例

下列範例藉由傳回 Adventure Works Cycles 公司的階層式員工清單 (從最高階的員工開始) 來顯示遞迴 CTE 結構的語意。執行 CTE 的陳述式會將結果集限制於研發部門的員工。範例後附有程式碼執行的逐步解說。

USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 
        0 AS Level
    FROM HumanResources.Employee AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
        Level + 1
    FROM HumanResources.Employee AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
    ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0;
GO

範例程式碼的逐步解說

遞迴 CTE DirectReports 定義了一個錨點成員與一個遞迴成員。

錨點成員傳回基底結果集 T0。這是公司中最高階的員工，亦即不需對主管報告的員工。
以下是錨點成員所傳回的結果集：

ManagerID EmployeeID Title                                   Level
--------- ---------- --------------------------------------- ------
NULL      109        Chief Executive Officer                 0

遞迴成員會傳回錨點成員結果集中之員工的直屬部屬。此動作由 Employee 資料表與 DirectReports CTE 之間的聯結作業所達成。遞迴引動過程即是由這項對 CTE 本身的參考所建立。根據 CTE DirectReports 中的員工，以 (Ti) 做為輸入，聯結 (Employee.ManagerID = DirectReports.EmployeeID) 會做為輸出 (Ti+1) 傳回，也就是主管為 (Ti) 的員工。因此，遞迴成員的第一次反覆運算會傳回下列結果集：
```
ManagerID EmployeeID Title                                   Level
--------- ---------- --------------------------------------- ------
109       12         Vice President of Engineering           1
```

遞迴成員會重複啟動。遞迴成員的第二次反覆運算使用步驟 3 中的單一資料列結果集 (包含 EmployeeID``12) 做為輸入值，並且傳回下列結果集：

ManagerID EmployeeID Title                                   Level
--------- ---------- --------------------------------------- ------
12        3          Engineering Manager                     2

遞迴成員的第三次反覆運算使用上述的單一資料列結果集 (包含 EmployeeID``3) 做為輸入值，並且傳回下列結果集：

ManagerID EmployeeID Title                                   Level
--------- ---------- --------------------------------------- ------
3         4          Senior Tool Designer                    3
3         9          Design Engineer                         3
3         11         Design Engineer                         3
3         158        Research and Development Manager        3
3         263        Senior Tool Designer                    3
3         267        Senior Design Engineer                  3
3         270        Design Engineer                         3

遞迴成員的第四次反覆運算使用 EmployeeID 值 4、9、11、158、263、267 與 270 的先前資料列集做為輸入值。
此程序會不斷重複，直到遞迴成員傳回空的結果集為止。

執行中查詢所傳回的最終結果集，是錨點成員與遞迴成員所產生的所有結果集的聯集。
以下是此範例所傳回的完整結果集：

ManagerID EmployeeID Title                                   Level
--------- ---------- --------------------------------------- ------
NULL      109        Chief Executive Officer                 0
109       12         Vice President of Engineering           1
12        3          Engineering Manager                     2
3         4          Senior Tool Designer                    3
3         9          Design Engineer                         3
3         11         Design Engineer                         3
3         158        Research and Development Manager        3
3         263        Senior Tool Designer                    3
3         267        Senior Design Engineer                  3
3         270        Design Engineer                         3
263       5          Tool Designer                           4
263       265        Tool Designer                           4
158       79         Research and Development Engineer       4
158       114        Research and Development Engineer       4
158       217        Research and Development Manager        4
(15 row(s) affected)

請參閱

共用方式為

使用一般資料表運算式的遞迴查詢

遞迴 CTE 的結構

虛擬程式碼與語意

範例

範例程式碼的逐步解說

請參閱

概念

其他資源

說明及資訊

其他資源