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

核心提示： WITHManagersAS(--initializationSELECTEmployeeID,LastName,ReportsToFROMEmployeesWHEREReportsToISNULLUNIONALL--recursiveexecutionSELECTe.employeeID

　　WITHManagersAS
　　(
　　--initialization
　　SELECTEmployeeID,LastName,ReportsTo
　　FROMEmployees
　　WHEREReportsToISNULL
　　UNIONALL
　　--recursiveexecution
　　SELECTe.employeeID,e.LastName,e.ReportsTo
　　FROMEmployeeseINNERJOINManagersm
　　ONe.ReportsTo=m.employeeID
　　)
　　SELECT*FROMManagers

代码浏览

递归CTE，Managers，定义了一个初始化查询和一个递归执行查询。

初始化查询返回基本结果，并且它是层级中的最高级。这是由ReportsTo 值为NULL来确认的，这意味着这个特定的Employee 不向任何人报告。根据这个表是怎么设计的，这个值可以是任何值，只要它代表层级中的最高级。

递归执行查询然后使用UNION ALL关键字将其连接到初始化查询。结果集是基于初始化查询返回的直接下属，它然后会显示为层级中的下一级。递归的发生是由于这个查询根据Managers CTE中的Employee参照这个CTE本身来作为输入。然后这个连接会返回以这个递归查询之前返回的记录为他们的经理的雇员。递归查询不断重复直到它返回一个空的结果集。

最终的结果集由查询这个Managers CTE 返回。

示例查询包含递归CTE必须包含的元素。而且它的代码更易读。这使得开发人员可以很方便地编写复杂的查询。

你还可以使用查询提示在一定数量的循环之后停止一个语句。这可以防止一个CTE由于代码较差而进入一个无限循环。在参照这个CTE的SELECT查询中包含MAXRECURSION关键字就可以做到了。为了在之前的例子中使用它：

SELECT * FROM Managers OPTION (MAXRECURSION 4)

为了在SQL Server 2000中创建一个生成相同结果的类似而不递归的查询，你的代码可能类似于下面的内容：

　　DECLARE@rowsAddedINT
　　--tablevariabletoholdaccumulatedresults
　　DECLARE@managersTABLE--initialize@managerswhodonothavemanagers
　　(EmpIDINT,MgrIDINT,processedINTDEFAULT(0))
　　INSERT@managers
　　SELECTEmployeeID,ReportsTo,0
　　FROMEmployees
　　WHEREReportsToISNULL
　　SET@rowsAdded=@@rowcount
　　--dothiswhilenewemployeesareaddedinthepreviousiteration
　　WHILE@rowsAdded>0
　　BEGIN
　　--markemployeerecordsgoingtobefoundinthisiterationwith--processed=1
　　UPDATE@managersSETprocessed=1WHEREprocessed=0
　　--insertemployeeswhoreporttoemployeesnotyetprocessed
　　INSERT@managers
　　SELECTEmployeeID,ReportsTo,0
　　FROMEmployeese
　　INNERJOIN@managersrONe.ReportsTo=r.EmpID
　　WHEREReportsTo<>EmployeeIDANDr.processed=1
　　SET@rowsAdded=@@rowcount
　　--markemployeerecordsfoundinthisiterationasprocessed
　　UPDATE@managersSETprocessed=2WHEREprocessed=1
　　END
　　SELECT*FROM@managers

总结

通过上面的例子，层级数据结构、组织图表和其它父子表管理报表可以很容易地从递归CTE的使用中获益。公共表表达式只是这些可用于SQL Server 2005的T-SQL增强之一。CTE给我们带来了创建更加复杂查询而语法更加简单的能力。它们还可以缩减在视图不可重用的情况下创建和测试视图的管理预算。