在SQL Server中使用公共表表达式的递归查询
2008-10-27 10:09:28 来源:WEB开发网 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给我们带来了创建更加复杂查询而语法更加简单的能力。它们还可以缩减在视图不可重用的情况下创建和测试视图的管理预算。
- ››sql server自动生成批量执行SQL脚本的批处理
- ››使用linux中的quota教程
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
- ››SQL Sever 2008 R2 数据库管理
- ››使用jxl生成带动态折线图的excel
- ››SQL SERVER无法安装成功,sqlstp.log文件提示[未发...
- ››Sql Server中通过父记录查找出所有关联的子记录
- ››SqlServer触发器、存储过程和函数
- ››SQL Server 中的事务(含义,属性,管理)
- ››Sqlite数据库插入和读取图片数据
更多精彩
赞助商链接