SQLite:从树的末端选择到顶部 [英] SQLite : Select from the end of tree to the top
本文介绍了SQLite:从树的末端选择到顶部的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
从这个很棒的答案,现在我可以正确地实现递归查询了,
from this awesome answer, now i can implement recursive query properly ,
我还有一个问题,
首先:
我创建了一个表:
CREATE TABLE tree(
id_tree integer PRIMARY KEY AUTOINCREMENT,
id_boss TEXT,
id_child TEXT,
answ TEXT);
插入一些值:
INSERT INTO tree(id_boss,id_child,answ) VALUES('1','2','T');
INSERT INTO tree(id_boss,id_child,answ) VALUES('1','3','F');
INSERT INTO tree(id_boss,id_child,answ) VALUES('2','P1','T');
INSERT INTO tree(id_boss,id_child,answ) VALUES('2','4','F');
INSERT INTO tree(id_boss,id_child,answ) VALUES('3','P2','T');
INSERT INTO tree(id_boss,id_child,answ) VALUES('3','8','F');
然后我从这个很棒的answer运行递归查询:
and then i running a recursive query from this awesome answer :
WITH RECURSIVE
under_alice(name,level,order_nr) AS (
VALUES('1','0',0)
UNION ALL
SELECT tree.id_child, under_alice.level+1, tree.id_tree
FROM tree, under_alice
WHERE tree.id_boss=under_alice.name
ORDER BY 2 DESC, 3
)
SELECT substr('..........',1,level*3) || name FROM under_alice;
结果是这样的:
1
...2
......P1
......4
...3
......P2
......8
已编辑" ------>
我的问题是,这是否可以扭转它,例如我选择 id_child = 'P2',结果将是:
and my question is, is this posible to reverse it, for example i choose id_child = 'P2', result will be :
P2
3
1
如果我选择 id_child = 'P1' :
and if i choose id_child = 'P1' :
P1
2
1
推荐答案
递归查询从P1
开始:
VALUES('P1')
在递归步骤中,我们通过查找该条目的 id_boss
值从某个记录 (ancestor
) 转到其父项:
In the recursion step, we go from some record (ancestor
) to its parent by looking up the id_boss
value of that entry:
SELECT tree.id_boss
FROM tree JOIN ancestor ON tree.id_child = ancestor.id
一切都在一起:
WITH RECURSIVE
ancestor(id) AS (
VALUES('P1')
UNION ALL
SELECT tree.id_boss
FROM tree JOIN ancestor ON tree.id_child = ancestor.id
)
SELECT id FROM ancestor;
这篇关于SQLite:从树的末端选择到顶部的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文