SQL CTE递归查询

哪位大侠帮忙看下下面这段语句的问题，执行后错误提示“语句被终止。完成执行语句前已用完最大递归 100”
USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
SELECT EmployeeID, ManagerID, Title
FROM HumanResources.Employee
WHERE ManagerID IS NOT NULL
UNION ALL
SELECT cte.EmployeeID, cte.ManagerID, cte.Title
FROM cte
JOIN HumanResources.Employee AS e
ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

如果我把 OPTION (MAXRECURSION 2);
改为 OPTION (MAXRECURSION 0);
可以执行，但是出现死循环，在表最后一条记录数出现死循环。

首先你在这里 OPTION (MAXRECURSION 2) 无非就是想控制递归次数
你可以这样做

--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title,lev) as
(
SELECT EmployeeID, ManagerID, Title,lev=1
FROM HumanResources.Employee
WHERE ManagerID IS NOT NULL
UNION ALL
SELECT cte.EmployeeID, cte.ManagerID, cte.Title,cte.lev+1
FROM cte
JOIN HumanResources.Employee AS e
ON cte.ManagerID = e.EmployeeID
where cte.lev<3
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

注意我这里多了 lev 通过lev的数值大小来控制循环递归次数

2.OPTION (MAXRECURSION 0);
可以执行，但是出现死循环，在表最后一条记录数出现死循环。
===》
那是因为你的递归没有出口

SQL CTE递归查询

您可能感兴趣问答

Collapsible

热门标签

热点问答