共用方式為


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

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

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

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

遞迴 CTE 的結構

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

遞迴 CTE 包含三個元素:

  1. 常式的引動過程。
    遞迴 CTE 的第一個引動過程包含由 UNION ALL、UNION、EXCEPT 或 INTERSECT 運算子聯結的一或多個 CTE_query_definitions。這些查詢定義會形成 CTE 結構的基底結果集,因此稱為錨點成員。
    CTE_query_definitions 若未參考 CTE 本身,即會被視為錨點成員。所有錨點成員查詢定義都必須位於第一個遞迴成員定義之前,且必須使用 UNION ALL 運算子來聯結最後一個錨點成員與第一個遞迴成員。
  2. 常式的遞迴引動過程。
    遞迴引動過程包含了由參考 CTE 本身之 UNION ALL 運算子所聯結的一或多個 CTE_query_definitions。這些查詢定義稱為遞迴成員。
  3. 終止檢查。
    終止檢查是隱含的;當先前的引動過程不傳回資料列時,遞迴就會停止。
ms186243.note(zh-tw,SQL.90).gif附註:
撰寫錯誤的遞迴 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

遞迴執行的語意如下:

  1. 將 CTE 運算式分割為錨點成員與遞迴成員。
  2. 執行錨點成員以建立第一個引動過程或基底結果集 (T0)。
  3. 執行遞迴成員,以 Ti 做為輸入,而以 Ti+1 做為輸出。
  4. 重複步驟 3,直到傳回空的結果集為止。
  5. 傳回結果集。這是 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

範例程式碼的逐步解說

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

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

    ManagerID EmployeeID Title                                   Level
    --------- ---------- --------------------------------------- ------
    NULL      109        Chief Executive Officer                 0
    
  3. 遞迴成員會傳回錨點成員結果集中之員工的直屬部屬。此動作由 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
    
  4. 遞迴成員會重複啟動。遞迴成員的第二次反覆運算使用步驟 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
    

    遞迴成員的第四次反覆運算使用 EmployeeID4911158263267270 的先前資料列集做為輸入值。
    此程序會不斷重複,直到遞迴成員傳回空的結果集為止。

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

    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)
    

請參閱

概念

使用一般資料表運算式

其他資源

WITH common_table_expression (Transact-SQL)
查詢提示 (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
DELETE (Transact-SQL)
EXCEPT 和 INTERSECT (Transact-SQL)

說明及資訊

取得 SQL Server 2005 協助