發行項
04/02/2012

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

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

參考遞迴 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 結構至少必須包含一個錨點成員與一個遞迴成員。下列虛擬程式碼顯示含有單一錨點成員與單一遞迴成員的簡單遞迴 CTE 所具備的元件。

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

(

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 結構的語意。範例後附有程式碼執行的逐步解說。

-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
    EmployeeID smallint NOT NULL,
    FirstName nvarchar(30)  NOT NULL,
    LastName  nvarchar(40) NOT NULL,
    Title nvarchar(50) NOT NULL,
    DeptID smallint NOT NULL,
    ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

USE AdventureWorks2008R2;
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 dbo.MyEmployees AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.BusinessEntityID 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 dbo.MyEmployees AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, DeptID, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
    ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing' OR Level = 0;
GO

範例程式碼的逐步解說

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

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

以下是錨點成員所傳回的結果集：

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

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

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

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2

遞迴成員的第三次反覆運算使用上述的結果集做為輸入值，並且傳回下列結果集：

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3

執行中查詢所傳回的最終結果集，是錨點成員與遞迴成員所產生之所有結果集的聯集。

以下是此範例所傳回的完整結果集：

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer       0
1         273        Vice President of Sales       1
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3