爱华网
SELECT* FROM locs order by loclevel
使用公用表表达式的递归查询
SQL Server 2008 R2其他版本公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE以返回数据子集直到获取完整结果集的公用表表达式。
当某个查询引用递归 CTE时，它即被称为递归查询。递归查询通常用于返回分层数据，例如：显示某个组织图中的雇员或物料清单方案（其中父级产品有一个或多个组件，而那些组件可能还有子组件，或者是其他父级产品的组件）中的数据。
递归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW语句中运行递归查询所需的代码。在 SQL Server的早期版本中，递归查询通常需要使用临时表、游标和逻辑来控制递归步骤流。有关公用表表达式的详细信息，请参阅使用公用表表达式。
递归CTE 的结构Transact-SQL 中的递归 CTE的结构与其他编程语言中的递归例程相似。尽管其他语言中的递归例程返回标量值，但递归 CTE 可以返回多行。
递归 CTE 由下列三个元素组成：
例程的调用。
递归 CTE 的第一个调用包括一个或多个由 UNION ALL、UNION、EXCEPT 或 INTERSECT运算符联接的CTE_query_definitions。由于这些查询定义形成了CTE 结构的基准结果集，所以它们被称为“定位点成员”。
CTE_query_definitions被视为定位点成员，除非它们引用了CTE 本身。所有定位点成员查询定义必须放置在第一个递归成员定义之前，而且必须使用 UNION ALL运算符联接最后一个定位点成员和第一个递归成员。
例程的递归调用。
递归调用包括一个或多个由引用 CTE 本身的 UNION ALL运算符联接的CTE_query_definitions。这些查询定义被称为“递归成员”。
终止检查。
终止检查是隐式的；当上一个调用中未返回行时，递归将停止。
| 注意 |
|---|
如果递归 CTE组合不正确，可能会导致无限循环。例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。在测试递归查询的结果时，可以通过在INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION 子句中使用 MAXRECURSION 提示和 0 到32,767 之间的值，来限制特定语句允许的递归级数。有关详细信息，请参阅查询提示(Transact-SQL)和WITHcommon_table_expression_r(Transact-SQL)。 |
伪代码和语义
递归 CTE 结构必须至少包含一个定位点成员和一个递归成员。以下伪代码显示了包含一个定位点成员和一个递归成员的简单递归 CTE的组件。
WITHcte_name ( column_name [,...n] )
AS
(
CTE_query_definition–- Anchor member is defined.
UNIONALL
CTE_query_definition–- Recursive member is defined referencing cte_name.
)
--Statement using the CTE
SELECT*
FROMcte_name
递归执行的语义如下：
将 CTE 表达式拆分为定位点成员和递归成员。
运行定位点成员，创建第一个调用或基准结果集 (T0)。
运行递归成员，将 Ti作为输入，将Ti+1作为输出。
重复步骤 3，直到返回空集。
返回结果集。这是对 T0到Tn执行UNIONALL 的结果。
以下示例通过返回 Adventure WorksCycles 公司的雇员的分层列表（从最高级雇员开始）显示递归 CTE 结构的语义。示例后面是代码执行的演练。
-- Create an Employee table. 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) ); -- Populate the table with values. 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
示例代码演练
递归 CTEDirectReports定义了一个定位点成员和一个递归成员。
定位点成员返回基准结果集 T0。这就是公司中的最高级雇员，即不向经理报告的雇员。
以下是定位点成员返回的结果集：
ManagerIDEmployeeIDTitleLevel ------------------------------------------------------ NULL1Chief Executive Officer0
递归成员返回定位点成员结果集中的雇员的直接下属。这是通过在Employee表和DirectReportsCTE之间执行联接操作获得的。正是此次对 CTE 自身的引用建立了递归调用。利用CTEDirectReports中的雇员作为输入(Ti)，联接(MyEmployees.ManagerID= DirectReports.EmployeeID) 返回经理为 (Ti)的雇员作为输出 (Ti+1)。这样，递归成员的第一次迭代返回了以下结果集：
ManagerIDEmployeeIDTitleLevel ------------------------------------------------------ 1273Vice President of Sales1
重复激活递归成员。递归成员的第二次迭代使用步骤 3中的单行结果集（包含EmployeeID273）作为输入值，并返回以下结果集：
ManagerIDEmployeeIDTitleLevel ------------------------------------------------------ 27316Marketing Manager2 273274North American Sales Manager2 273285Pacific Sales Manager2
递归成员的第三次迭代使用上面的结果集作为输入值，并返回以下结果集：
ManagerIDEmployeeIDTitleLevel ------------------------------------------------------ 1623Marketing Specialist3 274275Sales Representative3 274276Sales Representative3 285286Sales Representative3
正在运行的查询返回的最终结果集是定位点成员和递归成员生成的所有结果集的并集。
以下是示例返回的完整结果集：
ManagerIDEmployeeIDTitleLevel ------------------------------------------------------ NULL1Chief Executive Officer0 1273Vice President of Sales1 27316Marketing Manager2 273274North American Sales Manager2 273285Pacific Sales Manager2 1623Marketing Specialist3 274275Sales Representative3 274276Sales Representative3 285286Sales Representative3
