SQLite:从树的末端选择到顶部 [英] SQLite : Select from the end of tree to the top

问题描述

从这个很棒的答案，现在我可以正确地实现递归查询了，

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屋！