单父节点环境：雇员组织结构图
对于单父节点层次结构方案，使用雇员组织结构图。
注 本节中的示例使用一个名为 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
.Net 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