在SQL Server中使CTE执行递归查询，3分钟快速入门

问题

在SQL Server 2000中，您需要实现SQL Server递归查询以检索以分层格式显示的数据。通常，我们求助于实现视图，游标或临时表，并对它们执行查询。当层次结构级别随着SQL Server限于32个递归级别而增加时，就会出现问题。我们需要一种更好的方法来在SQL Server 2005及以上版本中实现递归查询。我们如何做到这一点？

递归查询 – 解决方法

公用表表达式（CTE）是SQL Server 2005中引入的，可以认为是在单个SELECT，INSERT，UPDATE，DELETE或CREATE VIEW语句的执行范围内定义的临时结果集。您可以将CTE视为派生表的改进版本，它与非持久性视图类型更相似。在SQL Server 2000中，将CTE视为派生表。可以使用与派生表相同的许多方式来使用CTE。CTE也可以包含对自己的引用。这使开发人员可以更轻松地编写复杂的查询。也可以使用CTE代替视图。CTE的使用提供了两个主要优点。一种是带有派生表定义的查询变得更加简单易读。尽管用于处理派生表的传统T-SQL构造通常需要对派生数据（例如临时表或表值函数）进行单独定义，但使用CTE可以更轻松地查看带有派生表的派生表的定义。使用它的代码。另一件事是CTE大大减少了遍历递归层次结构的查询所需的代码量。

要了解CTE的全部含义，首先让我们看一下在SQL Server 2005中创建CTE的语法。

语法

通常，递归CTE具有以下语法：

您为CTE的结果列提供一个别名和别名的可选列表，该关键字的后面是关键字WITH，该关键字通常基于查询定义来定义派生表。撰写CTE的正文；并从外部查询中引用它。

为了正确理解这一点，让我们举一个使用递归的简单示例。我们将查看罗斯文（Northwind）数据库中的“雇员”表，并看到特定雇员向另一名雇员报告。我们可以提出的一个问题是：“谁向谁报告？” 员工表的设计方式使得ReportsTo列是一个外键字段，它引用主键字段EmployeeID。因此，我们可以创建一个查询来回答我们的问题。使用CTE的示例查询如下所示。

代码演练

1. 递归CTE Managers定义了初始化查询和递归执行查询
2. 初始化查询返回基本结果，并且是层次结构中的最高级别。这由NULL 的ReportsTo值标识，这意味着特定的Employee不会向任何人报告。根据表的设计方式，该值可以是任何值，只要它表示层次结构中的最高级别
3. 然后，使用UNION ALL关键字将递归执行查询与初始化查询连接。结果集基于初始化查询返回的直接下级，然后直接显示为层次结构中的下一个级别。发生递归的原因是由于查询是基于Managers CTE中的Employee引用CTE本身作为输入的。然后，联接返回具有经理的雇员作为递归查询返回的先前记录。重复执行递归查询，直到返回空结果集。
4. 通过查询Managers CTE返回最终结果集 

示例查询包含递归CTE必须包含的元素。而且，代码更具可读性。这使开发人员可以轻松编写复杂的查询。

您还可以使用查询提示在定义的循环次数后停止语句。这可以阻止CTE在编码不良的语句上进入无限循环。为此，您可以在SELECT查询中引用CTE的方式包括MAXRECURSION关键字。在上一个示例中使用它

若要创建在SQL Server 2000中产生相同结果的类似但非递归查询，您可能会想出类似于以下代码的内容：

总结

给定上面的示例，层次结构的数据结构，组织结构图和其他父子表关系报告可以从递归CTE的使用中轻松受益。公用表表达式只是SQL Server 2005可用的T-SQL增强功能之一。CTE使我们有机会创建更复杂的SQL Server递归查询，同时保留更简单的语法。它们还可以减轻在视图不会被重用的情况下创建和测试视图的管理负担。