SQL Server 2005 Beta 2 Transact-SQL 增强功能(一)

作者：用户投稿来源：网络发布时间： 12/10/18

改为 James（而不是没有经理）：

UPDATE Employees SET mgrid = 14 WHERE empid = 1

以下循环被引入：1->3->7->11->14->1。如果您尝试运行返回 Nancy 及其所有级别的直接和间接下属的代码，则您会获得一个错误，表明默认的最大递归次数 100 在该语句完成之前耗尽：

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
Msg 530, Level 16, State 1, Line 1
Statement terminated. Maximum recursion 100 has been exhausted before statement completion

当然，具有能够预防无限递归调用的安全措施是很好的，但是 MAXRECURSION 在隔离循环和解决数据中的错误方面不能提供多少帮助。为了隔离循环，您可以使用相应的 CTE，以便为每个雇员构建通向该雇员的所有雇员 ID 的枚举路径。调用该结果列路径。在递归成员中，使用 CASE 表达式和 LIKE 谓词检查当前雇员 ID 是否已经出现在经理的路径中。如果答案是肯定的，则意味着您找到了循环。如果找到了循环，则在名为 cycle 的结果列中返回 1，否则返回 0。而且，向递归成员中添加一个筛选器，以确保只返回未检测到循环的经理的下属。最后，向外部查询中添加一个筛选器，以便只返回找到循环的雇员 (cycle = 1)：

WITH EmpCTE(empid, path, cycle)
AS
(
SELECT empid,
CAST(''.'' + CAST(empid AS VARCHAR(10)) + ''.'' AS VARCHAR(900)),
0
FROM Employees
WHERE empid = 1
UNION ALL

SELECT E.empid,
CAST(M.path + CAST(E.empid AS VARCHAR(10)) + ''.'' AS VARCHAR(900)),
CASE
WHEN M.path LIKE ''%.'' + CAST(E.empid AS VARCHAR(10)) + ''.%'' THEN 1
ELSE 0
END
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
WHERE M.cycle = 0

)
SELECT path FROM EmpCTE
WHERE cycle = 1
path
---------------
.1.3.7.11.14.1.

请注意，锚定成员和递归成员中的对应列必须具有相同的数据类型、长度和精度。这就是生成 path 值的表达式在两个成员中都被转换为 varbinary(900) 的原因。一旦检测到循环，您就可以通过将 Nancy 的经理重新更改为没有经理来修复数据中的错误：

UPDATE Employees SET mgrid = NULL WHERE empid = 1

迄今为止提供的递归示例具有一个经理锚定成员和一个用于检索下属的递归成员。某些请求则要求执行相反的操作;例如，请求返回 James 的管理路径（James 及其所有级别的经理）。以下代码提供了对该请求的应答：

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 14
UNION ALL

SELECT M.empid, M.empname, M.mgrid, E.lvl+1
FROM Employees as M
JOIN EmpCTE as E
ON M.empid = E.mgrid
)
SELECT * FROM EmpCTE

以下为结果集：

empid empname mgrid lvl
----------- ------------------------- ----------- -----------
14 James 11 0
11 David 7 1
7 Robert 3 2
3 Janet 1 3
1 Nancy NULL 4

锚定成员返回 James 的行。递归成员返回先前返回的雇员或经理的单个经理，因为这里使用的是单父节点层次结构并且请求从单个雇员开始。

您还可以使用递归查询来计算聚合，例如，直接或间接向每个经理汇报的下属的数量：

WITH MgrCTE(mgrid, lvl)
AS
(
SELECT mgrid, 0
FROM Employees
WHERE mgrid IS NOT NULL
UNION ALL
SELECT M.mgrid, lvl + 1
FROM Employees AS M
JOIN MgrCTE AS E
ON E.mgrid = M.empid
WHERE M.mgrid IS NOT NULL
)
SELECT mgrid, COUNT(*) AS cnt
FROM MgrCTE
GROUP BY mgrid

以下为结果集：

mgrid cnt
----------- -----------
1 13
2 2
3