SQLite : Select from the end of tree to the top

Question

来自这个很棒的 answer ，现在我可以正确地实现递归查询了，

我还有一个问题，

首先:

我创建了一个表:

 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 运行递归查询:

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'，结果将是:

  P2
  3
  1

如果我选择 id_child = 'P1' :

  P1
  2
  1

Answer 1

递归查询从P1开始:

VALUES('P1')

在递归步骤中，我们通过查找该条目的 id_boss 值从某个记录 (ancestor) 转到其父级:

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;