CTE查询引发递归耗尽错误

Question

下面的CTE查询抛出“语句已终止。在语句完成前已耗尽最大递归100”。

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

当在union all之前和之后执行子查询时，将获得以下结果。

锚点查询：-选择o.organizationid，组织作为组织从组织o内部联接存储s ON s.organizationid = o.organizationid结果：-

organizationid    |organization
--------------------------------
3                 | Org1

union all后查询：-

SELECT store.storeid，CAST(storeNAme AS NVARCHAR(50))作为存储中的storeNAme

结果：

StoreId           |StoreName
--------------------------------
3                 | Warehouse1

我可以知道原因吗？

Answer 1

在查询的末尾指定max递归选项：

...
from MyCTE
option (maxrecursion 0)

这允许您指定在生成错误之前CTE可以递归的频率。Maxrecursion 0允许无限递归。

Answer 2

在运行递归查询时，允许查询调用自身。查询返回多少行并不重要，重要的是查询调用自身的次数。因此，它有可能会陷入无限循环。为了防止这种情况发生，SQL Server有一个设置，该设置将确定允许的递归次数。此设置的默认值为100 -因此，当您的查询超过100次递归(自调用)时，您将收到此消息。

您可以通过在查询末尾添加OPTION (MAXRECURSION nn)来覆盖此设置。当nn为新的最大值时。

您也可以通过将此值设置为0来完全取消保护-它将如下所示：OPTION (MAXRECURSION 0)然而，不建议这样做，因为在运行时它将持续消耗资源。

OP询问如何避免潜在的无限循环，并为递归创建退出标准。

据我所知，您正在尝试构建数据层次结构。您可以考虑引入具有层次结构级别的附加列，并在某个级别停止(我在示例中选择10 )：

WITH MyCTE
     AS (
     SELECT o.organizationid,
            organization AS organization,
          1 lvl
     FROM organization o
          INNER JOIN store s ON s.organizationid = o.organizationid
     UNION ALL
     SELECT store.storeid,
            CAST(storeNAme AS NVARCHAR(50)) AS storeNAme,
          lvl+1
     FROM store
          INNER JOIN MyCTE ON store.organizationid = MyCTE.organizationid
     WHERE lvl<10
)
SELECT DISTINCT
    Organization
FROM MyCTE

Answer 3

我认为我们可以尝试这样来增加最大递归深度：

设置全局cte_max_recursion_depth=10000；

根据这里，https://forums.mysql.com/read.php?100,681245,681245