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

Find the entire tree from the root giving any node

提问by seesee

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

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

Example of 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
;

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

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

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.

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

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

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

回答by GWu

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

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

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

Edit:

编辑：

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

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

Example for non-root node:

非根节点示例：

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

Example for root node:

根节点示例：

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

小提琴在http://www.sqlfiddle.com/#!4/d15e7/32

回答by A.B.Cade

Why not just:

为什么不只是：

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

Hereis a fiddle

这是一个小提琴

EDIT
Here is another try (After understanding the full problem):

编辑
这是另一个尝试（了解完整问题后）：

with t as (
select case when mgr.employee_id is null then
1 else 0 end is_root, emp.employee_id employee, emp.manager_id manager, emp.last_name last_name

from employees mgr right outer join employees emp
on mgr.employee_id = emp.manager_id
),
tmp as (

select level, employee, last_name, manager ,
connect_by_root manager as root_id,
manager||sys_connect_by_path(employee,
',') cbp

from t
connect by prior employee = manager
start with t.is_root =
1 )
select * from tmp
where tmp.root_id in (select root_id from tmp where employee= 101 or manager = 101)

I checked it with 100, 101and 1010and it worked well
Hereis a fiddle

我检查了它100，101并且1010它运行良好
这是一个小提琴

回答by DARK LORD

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 in  ( 
  select employee_id from employees 
  where manager_id is null )