oracle - 从给定任何节点的根找到整棵树

Question

给定树的节点，如何找到整棵树？

树的例子:

       100
  101        102
1010 1011   1020  1021


select level, employee_id, last_name, manager_id ,
       connect_by_root employee_id as root_id
  from employees
 connect by prior employee_id = manager_id
 start with employee_id = 101
;

表中的根是 (parent,child) 示例 (100,101) 表中没有 (null,100) 行。

上面的查询只给出了从 101 开始的 child 。但是可以说我想要从根开始的所有内容吗？

当给定 '101' 作为节点时，您将不知道哪个是根。

当根是给定节点时，查询应该可用。

Answer 1

您需要先向上遍历树以获取所有经理，然后向下遍历以获取所有员工:

select level, employee_id, last_name, manager_id ,
       connect_by_root employee_id as root_id
   from employees
connect by prior employee_id = manager_id -- down the tree
start with manager_id in ( -- list up the tree
     select manager_id 
       from employees
     connect by employee_id = prior manager_id -- up the tree
     start with employee_id = 101
     )
;

见 http://www.sqlfiddle.com/#!4/d15e7/18

编辑:

如果给定节点也可能是根节点，请扩展查询以将给定节点包含在父节点列表中:

非根节点示例:

select distinct employee_id, last_name, manager_id 
   from employees
connect by prior employee_id = manager_id -- down the tree
start with manager_id in ( -- list up the tree
     select manager_id 
       from employees
     connect by employee_id = prior manager_id -- up the tree
     start with employee_id = 101
     union 
     select manager_id -- in case we are the root node
       from employees
     where manager_id = 101
     )
;

根节点示例:

select distinct employee_id, last_name, manager_id 
   from employees
connect by prior employee_id = manager_id -- down the tree
start with manager_id in ( -- list up the tree
     select manager_id 
       from employees
     connect by employee_id = prior manager_id -- up the tree
     start with employee_id = 100
     union 
     select manager_id -- in case we are the root node
       from employees
     where manager_id = 100
     )
;

fiddle 在 http://www.sqlfiddle.com/#!4/d15e7/32