WEB开发网
开发学院数据库MSSQL Server 在SQL Server中使用公共表表达式的递归查询 阅读

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

 2008-10-27 10:09:28 来源:WEB开发网   
核心提示: 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给我们带来了创建更加复杂查询而语法更加简单的能力。它们还可以缩减在视图不可重用的情况下创建和测试视图的管理预算。

上一页  1 2 

Tags:SQL Server 使用

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接