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

来源：互连网作者：出处：巧巧读书 2006-02-24 进入讨论组

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

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

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

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

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

USE tempdb -- or your own test database
CREATE TABLE Employees
(
empid   int         NOT NULL,
mgrid   int         NULL,
empname varchar(25) NOT NULL,
salary  money       NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
SET NOCOUNT ON
INSERT 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 所示。

SQL Server 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, 0
FROM Employees
WHERE empid = 7
UNION ALL
-- Recursive Member (RM)
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE

以下为结果集：

empid       empname                   mgrid       lvl
----------- ------------------------- ----------- -----------
7           Robert                    3           0
11          David                     7           1
12          Ron                       7           1
13          Dan                       7           1
14          James                     11          2

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

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

递归成员被反复激活，并且借助于 Employees 和 EmpCTE 之间的联接操作返回上一个结果的直接下属。Employees 代表下属，而 EmpCTE（它包含上一个调用的结果）代表经理：

?	首先，Robert 的下属被返回：David、Ron 和 James。
?	然后，David、Ron 和 Dan 的下属被返回：只有 James。
?	最后，James 的下属被返回：无 — 在这种情况下，递归终止。