在SQL Server中使用公共表表达式的递归查询

核心提示：问题 在SQL Server 2000中，你需要执行递归查询来获取以层级形式显示的数据，在SQL Server中使用公共表表达式的递归查询，我们一般采用执行视图、游标或导出表和对它们执行查询，当层级增加时问题就出现了，它参照主键字段EmployeeID ，因此，因为SQL Server仅限于32级递归，我们在SQL S

问题

在SQL Server 2000中，你需要执行递归查询来获取以层级形式显示的数据。我们一般采用执行视图、游标或导出表和对它们执行查询。当层级增加时问题就出现了，因为SQL Server仅限于32级递归。我们在SQL Server 2005中需要一个更好的方法来执行递归查询。我们该怎么做呢?

专家解答

公共表表达式(Common Table Expression，CTE)是SQL Server 2005推出的，并且可以看做是在单独的SELECT、INSERT、UPDATE、DELETE或CREATE VIEW语句执行范围内定义的临时结果集。在SQL Server 2000中将CTE看做是你的导出表。CTE可以以许多和你使用导出表一样的方式来使用。CTE还包括对自己的参照。这使得开发人员可以更简便地编写复杂的查询。CTE还用于替代视图。CTE的使用提供了两个主要的优点。一个是使用导出表定义的查询变得更加简单和易读。用来与导出表一起使用的传统T-SQL结构一般要求对导出数据提供一个单独的定义，例如临时表或表值函数，使用CTE更容易看到导出表的定义和使用它的代码。另外CTE显著地降低了递归层级间的查询所需的代码量。

要理解CTE是什么，让我们首先看看在SQL Server 2005中创建它的语法。

语法

一个普通的递归CTE具有以下语法：

　　WITHcte_alias(column_aliases)
　　AS
　　(
　　cte_query_definition--initialization
　　UNIONALL
　　cte_query_definition2--recursiveexecution
　　)
　　SELECT*FROMcte_alias

你提供具有一个别名和一个可选的别名列表的CTE使得它的结果字段遵循关键字WITH，它通常根据查询定义来定义导出表;编写CTE体部分;并从外部查询关联它。

为了将它放到合适的观察角度，让我们看一个使用了递归的简单例子。我们将看看Northwind 数据库中的Employees表并看看一个特定雇员向另一个雇员报告。我们遇到的一个问题是“谁报告给谁?”Employees 表被设计为有一个ReportsTo 字段是外键，它参照主键字段EmployeeID 。因此，我们可以创建一个查询来回答我们的问题。一个使用CTE的示例查询将看起来如下所示：