SQL Server中With As中递归的使用方法

1. 什么是With As（公用表表达式）

公用表表达式（Common Table Expression，简称CTE）是SQL Server 2005引入的一项重要功能，它允许在单个SQL语句的执行范围内定义临时结果集。CTE通过WITH关键字定义，语法结构如下：

WITH cte_name (column_name1, column_name2,...)
AS
(
    -- CTE查询定义
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
-- 主查询
SELECT * FROM cte_name;

CTE的主要优点包括： - 提高SQL语句的可读性和可维护性 - 可以替代视图，无需创建数据库对象 - 支持递归查询，这是其最强大的特性之一

2. 递归CTE的基本概念

递归CTE是一种特殊的CTE，它能够引用自身，从而实现递归查询。递归CTE在处理层次结构数据（如组织结构、产品分类、文件目录等）时特别有用。

递归CTE由三个关键部分组成： 1. 锚成员（Anchor Member）：这是递归的起点，返回基础结果集 2. 递归成员（Recursive Member）：引用CTE自身，通过UNION ALL与锚成员连接 3. 终止条件：确保递归不会无限进行

3. 递归CTE的语法结构

递归CTE的基本语法如下：

WITH RecursiveCTE AS
(
    -- 锚成员（基础查询）
    SELECT columns
    FROM table
    WHERE condition
    
    UNION ALL
    
    -- 递归成员（引用CTE自身）
    SELECT columns
    FROM table
    JOIN RecursiveCTE ON join_condition
    WHERE recursive_condition
)
SELECT * FROM RecursiveCTE
OPTION (MAXRECURSION number); -- 可选，限制递归深度

4. 递归CTE的实用示例

示例1：生成数字序列

WITH NumberSequence AS
(
    -- 锚成员：从1开始
    SELECT 1 AS Number
    
    UNION ALL
    
    -- 递归成员：每次加1，直到100
    SELECT Number + 1
    FROM NumberSequence
    WHERE Number < 100
)
SELECT Number FROM NumberSequence
OPTION (MAXRECURSION 100); -- 设置最大递归深度为100

示例2：查询组织结构层级

假设有一个员工表Employees，包含EmployeeID、Name和ManagerID字段：

WITH EmployeeHierarchy AS
(
    -- 锚成员：顶级管理者（没有上级经理）
    SELECT EmployeeID, Name, ManagerID, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- 递归成员：下级员工
    SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy
ORDER BY Level, Name;

示例3：查询产品分类树

假设有一个产品分类表Categories，包含CategoryID、Name和ParentCategoryID字段：

WITH CategoryTree AS
(
    -- 锚成员：顶级分类
    SELECT CategoryID, Name, ParentCategoryID, CAST(Name AS VARCHAR(1000)) AS Path
    FROM Categories
    WHERE ParentCategoryID IS NULL
    
    UNION ALL
    
    -- 递归成员：子分类
    SELECT c.CategoryID, c.Name, c.ParentCategoryID, 
           CAST(ct.Path + ' > ' + c.Name AS VARCHAR(1000)) AS Path
    FROM Categories c
    INNER JOIN CategoryTree ct ON c.ParentCategoryID = ct.CategoryID
)
SELECT * FROM CategoryTree
ORDER BY Path;

5. 递归CTE的高级用法

5.1 控制递归深度

SQL Server默认限制递归深度为100，可以通过OPTION子句修改：

-- 设置最大递归深度为500
OPTION (MAXRECURSION 500)

-- 取消递归深度限制（不推荐，可能导致无限循环）
OPTION (MAXRECURSION 0)

5.2 递归CTE中的聚合

可以在递归CTE中使用聚合函数计算层级汇总：

WITH SalesHierarchy AS
(
    -- 锚成员：销售代表
    SELECT EmployeeID, Name, ManagerID, 0 AS Level, SalesAmount
    FROM SalesStaff
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- 递归成员：经理及其团队
    SELECT s.EmployeeID, s.Name, s.ManagerID, sh.Level + 1, s.SalesAmount
    FROM SalesStaff s
    INNER JOIN SalesHierarchy sh ON s.ManagerID = sh.EmployeeID
)
SELECT ManagerID, SUM(SalesAmount) AS TeamSales
FROM SalesHierarchy
GROUP BY ManagerID;

5.3 递归CTE与PIVOT结合

可以将递归CTE与PIVOT操作结合，生成动态报表：

WITH DateSequence AS
(
    SELECT CAST('2023-01-01' AS DATE) AS DateValue
    
    UNION ALL
    
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateSequence
    WHERE DateValue < '2023-01-31'
),
SalesData AS
(
    SELECT d.DateValue, COALESCE(s.Amount, 0) AS Amount
    FROM DateSequence d
    LEFT JOIN Sales s ON d.DateValue = s.SaleDate
)
SELECT [Day], [1] AS Week1, [2] AS Week2, [3] AS Week3, [4] AS Week4, [5] AS Week5
FROM 
(
    SELECT 
        DATEPART(DAY, DateValue) AS [Day],
        DATEPART(WEEK, DateValue) - DATEPART(WEEK, DATEADD(MONTH, DATEDIFF(MONTH, 0, DateValue), 0)) + 1 AS [Week],
        Amount
    FROM SalesData
) AS SourceTable
PIVOT
(
    SUM(Amount)
    FOR [Week] IN ([1], [2], [3], [4], [5])
) AS PivotTable;

6. 递归CTE的性能优化

递归CTE虽然强大，但性能可能成为问题，特别是在处理大型数据集时。以下是一些优化建议：

1. 限制递归深度：使用MAXRECURSION选项防止过度递归
2. 添加适当的索引：确保递归查询中使用的连接字段有索引
3. 减少递归成员返回的列数：只选择必要的列
4. 考虑使用临时表：对于特别复杂的递归，可以先将锚成员结果存入临时表
5. 使用查询提示：如OPTION (OPTIMIZE FOR UNKNOWN)可能有助于某些情况

7. 递归CTE的限制

1. 递归CTE不能包含以下结构：

   • GROUP BY
   • HAVING
   • 左外连接（在递归成员中）
   • 子查询
   • TOP
   • 聚合函数（在递归成员中）

2. 递归成员只能引用CTE一次

3. SQL Server默认递归深度限制为100

8. 结论

递归CTE是SQL Server中处理层次结构和递归数据的强大工具。通过合理使用锚成员和递归成员，可以轻松解决许多复杂的查询问题，如组织结构遍历、产品分类导航、日期序列生成等。掌握递归CTE的使用方法，可以显著提高处理树形数据的效率和代码的可读性。

在实际应用中，应当注意递归深度控制和性能优化，确保查询既正确又高效。对于特别复杂的层次结构处理，也可以考虑结合使用递归CTE和其他SQL技术，如PIVOT、窗口函数等，以实现更强大的数据分析功能。