oracle Oracle中sql中的树结构。如何在SQL Oracle中显示树、子节点和父节点
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37134238/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Tree structure in sql in Oracle.How to show tree,child nodes and parent nodes in SQL Oracle
提问by Dev4All
I would like to show a tree structure in SQL with child nodes and parent nodes. I have a table like:
我想在 SQL 中显示一个带有子节点和父节点的树结构。我有一张像：
Employee
-------------
ID (int)
FirstName (varchar)
LastName (varchar)
ParentID (int)
Job (varchar)
which represents an employee. ParentIDrepresent the manager of the employee has. I would like to have this table only with this structure.
代表一名员工。ParentID代表该员工的经理拥有。我只想拥有这个结构的表。
- I would like to show the whole tree structure.
- I would like to show only the children nodes
- I would like to show only the parent nodes
- 我想展示整个树结构。
- 我只想显示子节点
- 我只想显示父节点
回答by MT0
Query - The whole tree structure:
查询 - 整个树结构：
SELECT *
FROM Employee
START WITH ParentID IS NULL
CONNECT BY PRIOR ID = ParentID
ORDER SIBLINGS BY LastName, FirstName, ID;
Query - The children of a given employee:
查询 - 给定员工的孩子：
You do not need a hierarchical query for this.
(The parent is given by the bind variable :parent_id)
您不需要为此进行分层查询。
（父级由绑定变量给出:parent_id）
SELECT *
FROM Employee
WHERE ParentID = :parent_id
ORDER BY LastName, FirstName, ID;
Query - The descendants of a given employee:
查询 - 给定员工的后代：
The same query as for the whole tree but with a different start point
(The parent is given by the bind variable :parent_id)
与整个树的查询相同，但起点不同
（父级由绑定变量给出:parent_id）
SELECT *
FROM Employee
START WITH ParentID = :parent_id
CONNECT BY PRIOR ID = ParentID
ORDER SIBLINGS BY LastName, FirstName, ID;
Query - The employee and their ancestors:
查询 - 员工及其祖先：
Similar to the previous query but with the CONNECT BYreversed 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)
与前面的查询类似，但CONNECT BY相反，您不需要订购兄弟姐妹，因为每个员工只有一个直接经理。
（员工由绑定变量给出:employee_id）
SELECT *
FROM Employee
START WITH ID = :employee_id
CONNECT BY PRIOR ParentID = ID;
Query - The employee's manager:
查询 - 员工的经理：
Identical to the previous query but with a filter LEVEL = 2to just get the immediate parent row.
(The employee is given by the bind variable :employee_id)
与上一个查询相同，但有一个过滤器LEVEL = 2来获取直接的父行。
（员工由绑定变量给出:employee_id）
SELECT e.*
FROM Employee e
WHERE LEVEL = 2
START WITH ID = :employee_id
CONNECT BY PRIOR ParentID = ID;
