如何限制CTE递归深度但选择通用表？

问题描述

当前，我们有一个存储过程，它通过执行以下操作从原始模式中的表返回数据：

Currently we have a stored procedure that returns data from a table in it's original schema by doing something like this:

WITH CTE AS
(
    -- Start CTE off by selecting the id that was provided to stored procedure.
    SELECT *
    FROM [dbo].[TestTable]
    WHERE [Id] = 1
    -- Recursively add tasks that are children of records already found in previous iterations.
    UNION ALL
    SELECT t.*
    FROM [dbo].[TestTable] as t
    INNER JOIN CTE as tcte
        ON t.[ParentId] = tcte.[Id]
)
SELECT *
FROM CTE

这很好，因为无论表架构如何更改，只要有[Id]和[ParentId]列，则无需更新此存储过程。我想做类似的事情，但是也可以动态指定递归的深度。我见过的唯一方法是添加一个Level / Depth标识符，如下所示：

This is nice, because no matter how the table schema changes, as long as there are [Id] and [ParentId] columns, I won't have to update this stored procedure. I'd like to do something similar, but also be able to specify the depth of the recursion dynamically. The only way I've seen to do this is to add a Level/Depth identifier like so:

WITH CTE AS
(
    -- Start CTE off by selecting the task that was provided to stored procedure.
    SELECT *, 0 as [Level]
    FROM [dbo].[TestTable]
    WHERE [Id] = 1
    -- Recursively add tasks that are children of parent tasks that have already been found in previous iterations.
    UNION ALL
    SELECT t.*, [Level] + 1
    FROM [dbo].[TestTable] as t
    INNER JOIN CTE as tcte
        ON t.[ParentId] = tcte.[Id]
    WHERE [Level] < 2
)
SELECT *
FROM CTE

这很好，但取消了上一个查询的主要内容，因为最后选择 * 也会获得相应的水平。还有其他方法可以在其中指定级别，但又可以从表中选择所有列吗？

This works well, but takes away the major plus of the previous query since selecting * at the end will give me the level as well. Is there some other way of doing this where I could specify a level, but also generically select all columns from the table? Thanks in advance.

推荐答案

如果您要对级别字段进行的所有操作都限制了递归次数，那么您应该可以使用，类似这样：

If all you want to do with your level field is limit the number of recursions, you should be able to use a MAXRECURSION query hint, something like this:

WITH Department_CTE AS
(
    SELECT
        DepartmentGroupKey,
        ParentDepartmentGroupKey,
        DepartmentGroupName
    FROM dimDepartmentGroup
    WHERE DepartmentGroupKey = 2
    UNION ALL
    SELECT
        Child.DepartmentGroupKey,
        Child.ParentDepartmentGroupKey,
        Child.DepartmentGroupName
    FROM Department_CTE AS Parent
        JOIN DimDepartmentGroup AS Child
            ON Parent.ParentDepartmentGroupKey = Child.DepartmentGroupKey
)
SELECT * FROM Department_CTE
OPTION (MAXRECURSION 2)

编辑：

要回答评论中的问题，不可以，您无法抑制递归次数超过MAXRECURSION设置所允许的错误。如果我对您的理解正确，则可以执行以下操作：

In answer to the question in the comments, no, you can't suppress the error that you get when recursing more times than your MAXRECURSION setting allows. If I understand you correctly, you could do something like this:

WITH CTE AS
(
    -- Start CTE off by selecting the task that was provided to stored procedure.
    SELECT Id, 0 as [Level]
    FROM [dbo].[TestTable]
    WHERE [Id] = 1
    -- Recursively add tasks that are children of parent tasks that have already been found in previous iterations.
    UNION ALL
    SELECT t.Id, [Level] + 1
    FROM [dbo].[TestTable] as t
    INNER JOIN CTE as tcte
        ON t.[ParentId] = tcte.[Id]
    WHERE [Level] < 2
),
CTE2 AS
(
    SELECT TestTable.*
    FROM CTE
        INNER JOIN TestTable ON CTE.Id = TestTable.Id
)
SELECT * FROM CTE2;

假设您不打算更改层次或主键字段。

This should be equally as generic as what you have above, assuming you're not planning on changing the hierarchical or primary key fields.

这篇关于如何限制CTE递归深度但选择通用表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！