如何停止CTE中的递归？ [英] How to stop recursion in a CTE?

问题描述

我有一个数据库表，如下所示：

I have a database table which looks like this:

ID                                   | PredecessorID                        | Data
-------------------------------------|--------------------------------------|-----------------
43b1e103-d8c6-40f9-b031-e5d9ef18a739 | null                                 | ...
55f6951b-5ed3-46c8-9ad5-64e496cb521a | 43b1e103-d8c6-40f9-b031-e5d9ef18a739 | ...
3eaa0889-31a6-449d-a499-e4beb9e4cad1 | 55f6951b-5ed3-46c8-9ad5-64e496cb521a | ...

我知道我可以使用（递归）通用表表达式（CTE）来获取我的数据的排序列表：

I know that I can use a (recursive) common table expression (CTE) to get a sorted list of my data:

WITH cte (ID, Data)
AS
(
  -- base case
  SELECT x.ID, x.Data
  FROM MyTable AS x
  WHERE x.PredecessorID IS NULL

  UNION ALL

  -- other cases
  SELECT x.ID, x.Data
  FROM MyTable as x
  INNER JOIN cte
    ON x.PredecessorID = cte.ID
)
SELECT * FROM cte

虽然这行得通，但如果我想获得全部表，我想知道如何只获取表的一部分，例如，从ID x 到ID y 。

While this works, if I want to get the entire table, I wonder how to only get a part of the table, say, everything between from ID x up to ID y.

很容易获得下界（我想）：只需更改基准的 WHERE 标准我想以ID开头的大小写：

Getting the lower bound right is easy (I assume): Simply change the WHERE criteria of the base case to the ID I want to start with:

  -- base case
  SELECT x.ID, x.Data
  FROM MyTable AS x
  WHERE x.PredecessorID='...'

但是上限呢？一旦达到ID为 y 的记录，我如何告诉CTE停止递归？

But what about the upper bound? How do I tell the CTE to stop recursing once the record with the ID y has been reached?

推荐答案

由于您要在此处进行迭代，并在递归项中获得它获取的cte的最后一个ID，因此您可以过滤掉上次迭代达到 y的结果

Since you are iterating here and have the cte's last id that it picked up available in your recursive term you can just filter out results where the last iteration hit 'y'

WITH cte (ID, Data)
AS
(
  -- base case
  SELECT x.ID, x.Data
  FROM MyTable AS x
  WHERE x.PredecessorID IS NULL

  UNION ALL

  -- other cases
  SELECT x.ID, x.Data
  FROM MyTable as x
  INNER JOIN cte
    ON x.PredecessorID = cte.ID
  WHERE cte.id <> 'y'
)
SELECT * FROM cte;

请注意，如果您的 x id有很多分支，其中一些不会导致 y，那么这些分支将不断迭代，直到达到其自然终点为止。只有通向 y 的分支会在这里过早停止。

Note that if your x id has many branches, some of which don't lead to 'y' then those branches will keep iterating until they hit their natural end. Only branch leading to y will be prematurely stopped here.

这篇关于如何停止CTE中的递归？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！