假设我们执行递归查询以通过树的邻接表获得某个树节点的子代,但仅获得一棵子树就足够了。
作为一个例子,我们将树的邻接表创建为
CREATE TABLE Tree
(parent INTEGER,child INTEGER);
INSERT INTO Tree
VALUES -- (parent -> child)
(1,2),(1,3),4),(2,5),11),(3,9),(5,6),7),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
添加到递归的锚点
WITH RECURSIVE children_i (parent,child)
AS (
-- anchor/initial values
SELECT parent,children_i
WHERE Tree.parent = children_i.child
)
SELECT * FROM children_i;
但是它会产生语法错误LIMIT clause should come after UNION ALL not before
(SQLite 3.16.2)。
如何在SQLite中实现这一目标?