Oracle中sql中的树结构.如何在SQL Oracle中显示树，子节点和父节点 [英] Tree structure in sql in Oracle.How to show tree,child nodes and parent nodes in SQL Oracle

问题描述

我想在SQL中显示带有子节点和父节点的树结构. 我有一张像这样的桌子:

I would like to show a tree structure in SQL with child nodes and parent nodes. I have a table like:

Employee
-------------
ID (int)
FirstName (varchar)
LastName (varchar)
ParentID (int)
Job (varchar)

代表员工. ParentID 代表员工的经理.我只想拥有这种结构的桌子.

which represents an employee. ParentID represent the manager of the employee has. I would like to have this table only with this structure.

1. 我想展示整个树的结构.
2. 我只想显示子节点
3. 我只想显示父节点

SampleDataImage

推荐答案

查询-整个树结构:

SELECT *
FROM   Employee
START WITH ParentID IS NULL
CONNECT BY PRIOR ID = ParentID
ORDER SIBLINGS BY LastName, FirstName, ID;

查询-给定员工的孩子:

您不需要为此进行分层查询.
(父级由绑定变量:parent_id给出)

You do not need a hierarchical query for this.
(The parent is given by the bind variable :parent_id)

SELECT *
FROM   Employee
WHERE  ParentID = :parent_id
ORDER BY LastName, FirstName, ID;

查询-给定员工的后代:

与整棵树相同的查询，但是起点不同
(父级由绑定变量:parent_id给出)

The same query as for the whole tree but with a different start point
(The parent is given by the bind variable :parent_id)

SELECT *
FROM   Employee
START WITH ParentID = :parent_id
CONNECT BY PRIOR ID = ParentID
ORDER SIBLINGS BY LastName, FirstName, ID;

查询-员工及其祖先:

与上一个查询类似，但将CONNECT BY取反，您无需订购兄弟姐妹，因为每个员工只有一名直属经理.
(雇员由绑定变量:employee_id给出)

Similar to the previous query but with the CONNECT BY reversed and you won't need to order the siblings as there will only be one immediate manager per employee.
(The employee is given by the bind variable :employee_id)

SELECT *
FROM   Employee
START WITH ID = :employee_id
CONNECT BY PRIOR ParentID = ID;

查询-员工经理:

与上一个查询相同，但具有过滤器LEVEL = 2以仅获取直接父行.
(雇员由绑定变量:employee_id给出)

Identical to the previous query but with a filter LEVEL = 2 to just get the immediate parent row.
(The employee is given by the bind variable :employee_id)

SELECT e.*
FROM   Employee e
WHERE  LEVEL = 2
START WITH ID = :employee_id
CONNECT BY PRIOR ParentID = ID;

这篇关于Oracle中sql中的树结构.如何在SQL Oracle中显示树，子节点和父节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！