单父节点环境雇员组织结构图
对于单父节点层次结构方案使用雇员组织结构图
注 本节中的示例使用一个名为 Employees 的表该表具有与 AdventureWorks 中的 HumanResourcesEmployee 表不同的结构您应当在自己的测试数据库或 tempdb 中运行代码而不要在 AdventureWorks 中运行代码
以下代码生成 Employees 表并且用示例数据填充它
USE tempdb or your own test database
CREATE TABLE Employees
(
empid int NOT NULL
mgrid int NULL
empname varchar() NOT NULL
salarymoney 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( NULL Nancy $)
INSERT INTO Employees VALUES( Andrew $)
INSERT INTO Employees VALUES( Janet $)
INSERT INTO Employees VALUES( Margaret $)
INSERT INTO Employees VALUES( Steven $)
INSERT INTO Employees VALUES( Michael $)
INSERT INTO Employees VALUES( Robert $)
INSERT INTO Employees VALUES( Laura $)
INSERT INTO Employees VALUES( Ann $)
INSERT INTO Employees VALUES( Ina $)
INSERT INTO Employees VALUES( David $)
INSERT INTO Employees VALUES( Ron $)
INSERT INTO Employees VALUES( Dan $)
INSERT INTO Employees VALUES( James $)
每个雇员都向其 ID 存储在 mgrid 列中的经理汇报在引用 empid 列的 mgrid 列上定义了一个外键这意味着经理 ID 必须对应于该表中的一个有效雇员 ID 或者为 NULL老板 Nancy 在 mgrid 列中具有 NULL 值经理雇员关系如图 所示
图 雇员组织结构图
下面是一些可能在 Employees 表上运行的常见请求
显示有关 Robert (empid=) 及其所有级别下属的详细信息
显示有关比 Janet (empid=) 低两个级别的所有雇员的详细信息
显示通向 James (empid=) 的管理链
显示有多少个雇员直接或间接向每个经理汇报
以适当的方式显示所有雇员以便可以容易地查看他们的层次依赖项
递归 CTE 提供了处理上述请求（它们在本质上是递归的）的手段而无须在数据库中维护有关层次结构的其他信息
第一个请求可能是最常见的一个请求返回某个雇员（例如empid= 的 Robert）及其所有级别的下属以下 CTE 提供了对该请求的解决方案
WITH EmpCTE(empid empname mgrid lvl)
AS
(
Anchor Member (AM)
SELECT empid empname mgrid
FROM Employees
WHERE empid =
UNION ALL
Recursive Member (RM)
SELECT Eempid Eempname Emgrid Mlvl+
FROM Employees AS E
JOIN EmpCTE AS M
ON Emgrid = Mempid
)
SELECT * FROM EmpCTE
以下为结果集
empid empname mgrid lvl
Robert
David
Ron
Dan
James
按照先前描述的递归 CTE 逻辑该 CTE 被按如下方式处理
锚定成员被激活并且从 Employees 表中返回 Robert 的行请注意在 lvl 结果列中返回的常量
递归成员被反复激活并且借助于 Employees 和 EmpCTE 之间的联接操作返回上一个结果的直接下属Employees 代表下属而 EmpCTE（它包含上一个调用的结果）代表经理
首先Robert 的下属被返回DavidRon 和 James
然后DavidRon 和 Dan 的下属被返回只有 James
最后James 的下属被返回无 — 在这种情况下递归终止
外部查询从 EmpCTE 中返回所有行
请注意对于每个递归调用lvl 值反复递增
使用该级别计数器您可以限制递归中的迭代次数例如以下 CTE 用来返回比 Janet 低两个级别的所有雇员
WITH EmpCTEJanet(empid empname mgrid lvl)
AS
(
SELECT empid empname mgrid
FROM Employees
WHERE empid =
UNION ALL
SELECT Eempid Eempname Emgrid Mlvl+
FROM Employees as E
JOIN EmpCTEJanet as M
ON Emgrid = Mempid
WHERE lvl <
)
SELECT empid empname
FROM EmpCTEJanet
WHERE lvl =
以下为结果集
empid empname
David
Ron
Dan
该代码示例中比上一个代码示例增加的代码以粗体显示递归成员中的筛选器 WHERE lvl < 被用作递归终止检查 — 当 lvl = 时不会返回任何行因而递归停止外部查询中的筛选器 WHERE lvl = 用来移除上至级别 的所有级别请注意从逻辑上讲外部查询中的筛选器 (lvl = ) 本身就足以只返回所需的行递归成员中的筛选器 (lvl < ) 是出于性能原因而添加的 — 为了在返回 Janet 下的两个级别之后立即停止递归
正如前面提到的那样CTE 可以引用在同一批处理中定义的本地变量例如为了使查询更一般化您可以使用变量而不是常量作为雇员 ID 和级别
DECLARE @empid AS INT @lvl AS INT
SET @empid = Janet
SET @lvl = two levels
WITH EmpCTE(empid empname mgrid lvl)
AS
(
SELECT empid empname mgrid
FROM Employees
WHERE empid = @empid
UNION ALL
SELECT Eempid Eempname Emgrid Mlvl+
FROM Employees as E
JOIN EmpCTE as M
ON Emgrid = Mempid
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
FROM Employees
WHERE empid =
UNION ALL
SELECT Eempid Eempname Emgrid Mlvl+
FROM Employees as E
JOIN EmpCTE as M
ON Emgrid = Mempid
)
SELECT * FROM EmpCTE
OPTION (MAXRECURSION )
以下为结果集
empid empname mgrid lvl
Nancy NULL
Andrew
Janet
Margaret
Ina
Robert
Laura
Ann
Net SqlClient Data Provider: Msg Level State Line
Statement terminated Maximum recursion has been exhausted before statement completion
可能返回（但是不能保证返回）迄今生成的结果并且生成错误 您可能会想到使用 MAXRECURSION 选项实现以下请求使用 MAXRECURSION 提示而不是递归成员中的筛选器返回 Janet 下两个级别的雇员
WITH EmpCTE(empid empname mgrid lvl)
AS
(
SELECT empid empname mgrid
FROM Employees
WHERE empid =
UNION
