SQL Server 2005 Beta 2 Transact-SQL 增强功能 1

核心提示： 下列各节介绍递归 CTE 在单父节点和多父节点环境中的实际示例和用法，单父节点环境：雇员组织结构图对于单父节点层次结构方案，SQL Server 2005 Beta 2 Transact-SQL 增强功能 1(6)，使用雇员组织结构图，注 本节中的示例使用一个名为 Employees 的表， 请注意，对于每个递归调用

下列各节介绍递归 CTE 在单父节点和多父节点环境中的实际示例和用法。

单父节点环境：雇员组织结构图

对于单父节点层次结构方案，使用雇员组织结构图。

注 本节中的示例使用一个名为 Employees 的表，该表具有与 AdventureWorks 中的 HumanResources.Employee 表不同的结构。您应当在自己的测试数据库或 tempdb 中运行代码，而不要在 AdventureWorks 中运行代码。

以下代码生成 Employees 表并且用示例数据填充它：

USE tempdb -- or your own test databaseCREATE TABLE Employees(empid   int         NOT NULL,mgrid   int         NULL,empname varchar(25) NOT NULL,salary  money       NOT NULL,CONSTRAINT PK_Employees Prima(最完善的虚拟主机管理系统)RY KEY(empid),CONSTRAINT FK_Employees_mgrid_empidFOREIGN KEY(mgrid)REFERENCES Employees(empid))CREATE INDEX idx_nci_mgrid ON Employees(mgrid)SET NOCOUNT ONINSERT INTO Employees VALUES(1 , NULL, 'Nancy'   , $10000.00)INSERT INTO Employees VALUES(2 , 1   , 'Andrew'  , $5000.00)INSERT INTO Employees VALUES(3 , 1   , 'Janet'   , $5000.00)INSERT INTO Employees VALUES(4 , 1   , 'Margaret', $5000.00)INSERT INTO Employees VALUES(5 , 2   , 'Steven'  , $2500.00)INSERT INTO Employees VALUES(6 , 2   , 'Michael' , $2500.00)INSERT INTO Employees VALUES(7 , 3   , 'Robert'  , $2500.00)INSERT INTO Employees VALUES(8 , 3   , 'Laura'   , $2500.00)INSERT INTO Employees VALUES(9 , 3   , 'Ann'     , $2500.00)INSERT INTO Employees VALUES(10, 4   , 'Ina'     , $2500.00)INSERT INTO Employees VALUES(11, 7   , 'David'   , $2000.00)INSERT INTO Employees VALUES(12, 7   , 'Ron'     , $2000.00)INSERT INTO Employees VALUES(13, 7   , 'Dan'     , $2000.00)INSERT INTO Employees VALUES(14, 11  , 'James'   , $1500.00)

每个雇员都向其 ID 存储在 mgrid 列中的经理汇报。在引用 empid 列的 mgrid 列上定义了一个外键，这意味着经理 ID 必须对应于该表中的一个有效雇员 ID 或者为 NULL。老板 Nancy 在 mgrid 列中具有 NULL 值。经理-雇员关系如图 1 所示。

(WINDOWS平台上强大的数据库平台) 2005 Beta 2 Transact-SQL 增强功能 1（图一）" />

图 1. 雇员组织结构图

 

下面是一些可能在 Employees 表上运行的常见请求：

?	显示有关 Robert (empid=7) 及其所有级别下属的详细信息。
?	显示有关比 Janet (empid=3) 低两个级别的所有雇员的详细信息。
?	显示通向 James (empid=14) 的管理链。
?	显示有多少个雇员直接或间接向每个经理汇报。
?	以适当的方式显示所有雇员，以便可以容易地查看他们的层次依赖项。

递归 CTE 提供了处理上述请求（它们在本质上是递归的）的手段，而无须在数据库中维护有关层次结构的其他信息。

第一个请求可能是最常见的一个请求：返回某个雇员（例如，empid=7 的 Robert）及其所有级别的下属。以下 CTE 提供了对该请求的解决方案：

WITH EmpCTE(empid, empname, mgrid, lvl)AS(-- Anchor Member (AM)SELECT empid, empname, mgrid, 0FROM EmployeesWHERE empid = 7UNION ALL-- Recursive Member (RM)SELECT E.empid, E.empname, E.mgrid, M.lvl+1FROM Employees AS EJOIN EmpCTE AS MON E.mgrid = M.empid)SELECT * FROM EmpCTE

以下为结果集：

empid       empname                   mgrid       lvl----------- ------------------------- ----------- -----------7           Robert                    3           011          David                     7           112          Ron                       7           113          Dan                       7           114          James                     11          2

按照先前描述的递归 CTE 逻辑，该 CTE 被按如下方式处理：

锚定成员被激活，并且从 Employees 表中返回 Robert 的行。请注意在 lvl 结果列中返回的常量 0。

1.	递归成员被反复激活，并且借助于 Employees 和 EmpCTE 之间的联接操作返回上一个结果的直接下属。Employees 代表下属，而 EmpCTE（它包含上一个调用的结果）代表经理： ? 首先，Robert 的下属被返回：David、Ron 和 James。 ? 然后，David、Ron 和 Dan 的下属被返回：只有 James。 ? 最后，James 的下属被返回：无 — 在这种情况下，递归终止。
2.	外部查询从 EmpCTE 中返回所有行。

请注意，对于每个递归调用，lvl 值反复递增。