单父节点环境：雇员组织结构图 对于单父节点层次结构方案，使用雇员组织结构图。
注 本节中的示例使用一个名为 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 所示。
图 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。
1. 递归成员被反复激活，并且借助于 Employees 和 EmpCTE 之间的联接操作返回上一个结果的直接下属。Employees 代表下属，而 EmpCTE（它包含上一个调用的结果）代表经理：
首先，Robert 的下属被返回：David、Ron 和 James。
然后，David、Ron 和 Dan 的下属被返回：只有 James。
最后，James 的下属被返回：无 — 在这种情况下，递归终止。
2. 外部查询从 EmpCTE 中返回所有行。
请注意，对于每个递归调用，lvl 值反复递增。
使用该级别计数器，您可以限制递归中的迭代次数。例如，以下 CTE 用来返回比 Janet 低两个级别的所有雇员：
WITH EmpCTEJanet(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 3
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTEJanet as M
ON E.mgrid = M.empid
WHERE lvl < 2
)
SELECT empid, empname
FROM EmpCTEJanet
WHERE lvl = 2
以下为结果集：
empid empname
----------- -------------------------
11 David
12 Ron
13 Dan
该代码示例中比上一个代码示例增加的代码以粗体显示。递归成员中的筛选器 WHERE lvl < 2 被用作递归终止检查 — 当 lvl = 2 时，不会返回任何行，因而递归停止。外部查询中的筛选器 WHERE lvl = 2 用来移除上至级别 2 的所有级别。请注意，从逻辑上讲，外部查询中的筛选器 (lvl = 2) 本身就足以只返回所需的行。递归成员中的筛选器 (lvl < 2) 是出于性能原因而添加的 — 为了在返回 Janet 下的两个级别之后立即停止递归。
正如前面提到的那样，CTE 可以引用在同一批处理中定义的本地变量。例如，为了使查询更一般化，您可以使用变量而不是常量作为雇员 ID 和级别：
DECLARE @empid AS INT, @lvl AS INT
SET @empid = 3 -- Janet
SET @lvl = 2 -- two levels
WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = @empid
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTE as M
ON E.mgrid = M.empid
WHERE lvl < @lvl
)
SELECT empid, empname
FROM EmpCTE
WHERE lvl = @lvl
您可以使用提示在已经调用一定数量的递归迭代之后强行终止查询。可以通过在外部查询的结尾添加 OPTION(MAXRECURSION value) 做到这一点，如以下示例所示：
WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 1
UNION ALL
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
OPTION (MAXRECURSION 2)
以下为结果集：
empid empname mgrid lvl
----------- ------------------------- ----------- -----------
1 Nancy NULL 0
2 Andrew 1 1
3 Janet 1 1
4 Margaret 1 1
10 Ina 4 2
7 Robert 3 2
8 Laura 3 2
9 Ann 3 2
SqlClient Data Provider: Msg 530, Level 16, State 1, Line 1
Statement terminated. Maximum recursion 2 has been exhausted before statement completion
可能返回（但是不能保证返回）迄今生成的结果，并且生成错误 530。您可能会想到使用 MAXRECURSION 选项实现以下请求：使用 MAXRECURSION 提示而不是递归成员中的筛选器返回 Janet 下两个级别的雇员：
WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 3
UNION
【责编:admin】
--------------------next---------------------
