sqlite - 限制递归查询的初始( anchor )值(with 语句)

Question

假设我们通过树的邻接表进行递归查询来获取某个树节点的子节点，但只获取单个子树就足够了。

作为示例，我们将树的邻接表创建为

CREATE TABLE Tree
       (parent INTEGER,
        child  INTEGER);

INSERT INTO Tree
       VALUES -- (parent -> child)
              (1, 2), (1, 3), (1, 4),
              (2, 5), (2, 11), (3, 9),
              (5, 6), (5, 7),  (5, 8),
              (9, 10), (11, 12);

然后进行递归查询以获得节点2的子节点:

WITH RECURSIVE children_i (parent, child)
AS (
   -- anchor/initial values
   VALUES (NULL, 2)
   -- SELECT parent, child FROM Tree WHERE parent = 2 LIMIT 1
   UNION ALL
   -- recursion
   SELECT children_i.child, Tree.child FROM Tree, children_i
   WHERE Tree.parent = children_i.child
   )
SELECT * FROM children_i;

这将产生

|2
2|5
2|11
5|6
5|7
5|8
11|12

现在我们如何将上面的查询限制为仅遵循单个子树(例如仅 2->5->{6, 7, 8} 而不是 2->11)？我尝试将 LIMIT 添加到递归的 anchor 部分，

WITH RECURSIVE children_i (parent, child)
AS (
   -- anchor/initial values
   SELECT parent, child FROM Tree WHERE parent = 2 LIMIT 1
   UNION ALL
   -- recursion
   SELECT children_i.child, Tree.child FROM Tree, children_i
   WHERE Tree.parent = children_i.child
   )
SELECT * FROM children_i;

但它会产生语法错误，LIMIT 子句应该出现在 UNION ALL 之后而不是之前 (SQLite 3.16.2)。

如何在 SQLite 中实现这一目标？

Answer 1

您可以使用LIMIT，但您需要将其提取以单独的cte:

WITH anchor AS (
  SELECT parent, child
  FROM tree
  WHERE parent = 2
  -- ORDER BY ...
  LIMIT 1
), children_i(parent,child) AS (
  -- anchor/initial values
  SELECT parent, child
  FROM anchor
  UNION ALL
  -- recursion
  SELECT c1.child, t1.child
  FROM tree t1
  JOIN children_i c1
    ON t1.parent = c1.child
)
SELECT * FROM children_i;

<强> db<>fiddle demo