使用通用表表达式的递归查询（Transact-SQL）

2025-07-29

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics 分析平台系统(PDW)Microsoft Fabric 中的 SQL 分析终结点 Microsoft Fabric 中的仓库 Microsoft Fabric 预览版中的 SQL 数据库

公共表表达式（CTE）提供了能够引用自身的重要优势，从而创建递归 CTE。递归 CTE 是重复执行初始 CTE 以返回数据的子集，直到获取完整的结果集。

查询引用递归 CTE 时称为递归查询。返回分层数据是递归查询的常见用法。例如，在组织结构图中显示员工，或者在父产品具有一个或多个组件且这些组件可能具有子组件或可能是其他父级的组件的材料帐单方案中显示员工。

递归 CTE 可以大大简化在、、SELECTINSERT或UPDATEDELETE语句中CREATE VIEW运行递归查询所需的代码。在早期版本的 SQL Server 中，递归查询通常需要使用临时表、游标和逻辑来控制递归步骤的流。有关常见表表达式的详细信息，请参阅 WITH common_table_expression。

递归 CTE 的结构

Transact-SQL 中的递归 CTE 的结构与其他编程语言中的递归例程类似。尽管其他语言中的递归例程返回标量值，但递归 CTE 可以返回多行。

递归 CTE 由三个元素组成：

调用例程。

递归 CTE 的第一个调用由、UNION ALL、UNION或EXCEPT运算符联接的INTERSECT一个或多个 CTE 查询定义组成。由于这些查询定义构成了 CTE 结构的基本结果集，因此它们称为定位点成员。

除非 CTE 本身引用 CTE 本身，否则 CTE 查询定义被视为定位点成员。所有定位点成员查询定义都必须放在第一个递归成员定义之前，并且必须使用运算符将最后一个 UNION ALL 定位点成员与第一个递归成员联接。
对例程的递归调用。

递归调用包括由引用 CTE 本身的运算符联接的 UNION ALL 一个或多个 CTE 查询定义。这些查询定义称为递归成员。
Termination check.

终止检查是隐式的;当前一次调用未返回任何行时，递归会停止。

Note

错误组合的递归 CTE 可能会导致无限循环。例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。在测试递归查询的结果时，可以使用提示和子句MAXRECURSIONOPTIONINSERTUPDATE中的 DELETE 0 到 32,767 之间的值来限制特定语句SELECT允许的递归级别数。

有关详细信息，请参见:

伪代码和语义

递归 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）的定位点成员。
以输入的形式运行递归成员，并将 TiTi + 1 作为输出运行。
重复步骤 3，直到返回空集。
返回结果集。这是一个UNION ALLT0。Tn

Examples

以下示例演示递归 CTE 结构的语义，方法是返回从数据库中排名最高的雇员 AdventureWorks2022 开始的员工层次结构列表。代码执行的演练遵循示例。

创建员工表：

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)
);

使用值填充表：

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