公用表表達式 (CTE) 具有一個重要的優點，那就是能夠引用其自身，從而創建遞歸 CTE。遞歸 CTE 是一個重覆執行初始 CTE 以返回數據子集直到獲取完整結果集的公用表表達式。

當某個查詢引用遞歸 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. 終止檢查。
   
   終止檢查是隱式的；當上一個調用中未返回行時，遞歸將停止。

偽代碼和語義

遞歸 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. 運行定位點成員，創建第一個調用或基準結果集 (T₀)。
   
   
3. 運行遞歸成員，將 T_i 作為輸入，將 T_i+1 作為輸出。
   
   
4. 重覆步驟 3，直到返回空集。
   
   
5. 返回結果集。這是對 T₀ 到 T_n 執行 UNION ALL 的結果。

實例

-- 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'Snchez', 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