从给定任何节点的根开始查找整个树

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

树的例子：

       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 开始的子项。但是假设我想要从根开始的所有内容？

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

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

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

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
     )
;

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

Edit:

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

非根节点示例：

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
     )
;

拨弄http://www.sqlfiddle.com/#!4/d15e7/32