CTE 由表示 CTE 的表达式名称、可选列列表和定义 CET 的查询组成。定义 CTE 后，可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中对其进行引用，就像引用表或视图一样。CTE 也可用于 CREATE VIEW 语句，作为定义 SELECT 语句的一部分。

CTE 的基本语法结构如下：

WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

只有在查询定义中为所有结果列都提供了不同的名称时，列名称列表才是可选的。

运行 CTE 的语句为：

SELECT <column_list>
FROM expression_name

示例

下面的示例显示了 CTE 结构的组件：表达式名称、列列表和查询。CTE 表达式 Sales_CTE 包含三个列（SalesPersonID、NumberOfOrders 和 MaxDate），在每个销售人员 SalesOrderHeader 表中被定义为销售订单总数和最近销售订单日期。执行语句时，CTE 将被引用两次：一次返回为销售人员所选的列，另一次检索销售经理的类似详细信息。销售人员和销售经理的数据都返回在一行中。

USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

以下为部分结果集：

EmployeeID  NumberOfOrders MaxDate  ManagerID NumberOfOrders MaxDate
----------- -------------- ---------- --------- -------------- ----------
268         48             2004-06-01 273       NULL           NULL
275         450            2004-06-01 268       48             2004-06-01
276         418            2004-06-01 268       48             2004-06-01
277         473            2004-06-01 268       48             2004-06-01

 

使用公用表表达式的递归查询

 公用表表达式 (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. 终止检查。
   终止检查是隐式的；当上一个调用中未返回行时，递归将停止。

注意：如果递归 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. 运行定位点成员，创建第一个调用或基准结果集 (T₀)。
3. 运行递归成员，将 T_i 作为输入，将 T_i+1 作为输出。
4. 重复步骤 3，直到返回空集。
5. 返回结果集。这是对 T₀ 到 T_n 执行 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. 定位点成员返回基准结果集 T₀。这就是公司中的最高级雇员，即不向经理报告的雇员。
以下是定位点成员返回的结果集：

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

3. 递归成员返回定位点成员结果集中的雇员的直接下属。这是通过在 Employee 表和 DirectReports CTE 之间执行联接操作获得的。正是此次对 CTE 自身的引用建立了递归调用。利用 CTE DirectReports 中的雇员作为输入 (T_i)，联接 (Employee.ManagerID = DirectReports.EmployeeID) 返回经理为 (T_i) 的雇员作为输出 (T_i+1)。这样，递归成员的第一次迭代返回了以下结果集：

ManagerID EmployeeID Title                                   Level
--------- ---------- --------------------------------------- ------
109       12         Vice President of Engineering           1

4. 重复激活递归成员。递归成员的第二次迭代使用步骤 3 中的单行结果集（包含 EmployeeID12）作为输入值，并返回以下结果集：

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

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)