从给出任何节点的根中查找整棵树 [英] Find the entire tree from the root giving any node

问题描述

在给定一个树的节点的情况下，如何查找整棵树?

How do I find the entire tree given a node of a tree?

树的示例:

       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)行.

The root in table is (parent,child) example (100,101) there is no (null,100) row in table.

上面的查询仅给出了从101开始的子代.但是可以说我想要从根开始的所有内容吗?

The above query only gives the children starting from 101. But lets say I want everything from the start of the root?

以"101"作为节点时，您将不知道哪个是根.

When given '101' as the node, you won't know which is the root.

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

The query should be usable when the root is the given node.

推荐答案

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

You need to first traverse up the tree to get all managers then traverse down to fetch all employees:

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

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

If the given node might also be the root node, extend the query to include the given node in the list of parent nodes:

非根节点的示例:

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 at http://www.sqlfiddle.com/#!4/d15e7/32

这篇关于从给出任何节点的根中查找整棵树的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！