CTE查询引发递归用尽错误 [英] CTE Query throws Recursion Exhausted error
问题描述
以下CTE查询将引发语句终止。在语句完成之前,最大递归100已用尽。
The following CTE Query throws "The statement terminated. The maximum recursion 100 has been exhausted before statement completion."
WITH MyCTE
AS (
SELECT o.organizationid,
organization AS organization
FROM organization o
INNER JOIN store s ON s.organizationid = o.organizationid
UNION ALL
SELECT store.storeid,
CAST(storeNAme AS NVARCHAR(50)) AS storeNAme
FROM store
INNER JOIN MyCTE ON store.organizationid = MyCTE.organizationid)
SELECT DISTINCT
Organization
FROM MyCTE
执行时
锚点查询:-
SELECT o.organizationid,
的组织AS的组织
来自组织机构o
内部联接商店s s.organizationid = o.organizationid
结果:-
Anchor query:- SELECT o.organizationid, organization AS organization FROM organization o INNER JOIN store s ON s.organizationid = o.organizationid Result:-
organizationid |organization
--------------------------------
3 | Org1
在合并全部后进行查询:-
query after union all:-
SELECT store.storeid,
CAST(storeNAme AS NVARCHAR(50))AS storeNAme
FROM store
SELECT store.storeid, CAST(storeNAme AS NVARCHAR(50)) AS storeNAme FROM store
结果:-
StoreId |StoreName
--------------------------------
3 | Warehouse1
我可以知道为什么吗?
推荐答案
在查询结束时指定maxrecursion选项:
Specify the maxrecursion option at the end of the query:
...
from MyCTE
option (maxrecursion 0)
您可以指定如何通常CTE可以在产生错误之前递归。 Maxrecursion 0允许无限递归。
That allows you to specify how often the CTE can recurse before generating an error. Maxrecursion 0 allows infinite recursion.
这篇关于CTE查询引发递归用尽错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!