oracle Connect by 子句以获取层次结构的顶部
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18110471/
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
Connect by clause to get the top of hierarchy
提问by user2133005
I am facing an issue while using the CONNECT BYclause in Oracle for finding hierarchical data. Let me give an example: A is my parent part which has child part B and B also has a child part C. When I am using the CONNECT BYclause I am able to get all the three levels but I only want the top most level, i.e. A.
我在使用CONNECT BYOracle 中的子句查找分层数据时遇到问题。让我举个例子：A 是我的父部分，它有子部分 B，B 也有一个子部分 C。当我使用这个CONNECT BY子句时，我能够获得所有三个级别，但我只想要最高级别，即一种。
回答by Ed Gibbs
Oracle has a LEVELpseudocolumnthat you can use:
Oracle 有一个可以使用的LEVEL伪列：
SELECT
myTable.ID,
myTable.ParentID
FROM myTable
WHERE LEVEL = 1
CONNECT BY PRIOR myTable.ID = myTable.ParentID
To find a top-level (root) value from any level, precede the column name with the CONNECT_BY_ROOToperator:
要从任何级别查找顶级（根）值，请在列名之前使用CONNECT_BY_ROOT运算符：
SELECT
myTable.ID,
myTable.ParentID,
CONNECT_BY_ROOT myTable.ID AS "Top Level ID"
FROM myTable
CONNECT BY PRIOR myTable.ID = myTable.ParentID
回答by Ole
I am adding this solution for tables with one or multiple trees (hierarchical data).
我正在为具有一棵或多棵树（分层数据）的表添加此解决方案。
Starting with one node (row) somewhere in the tree (hierarchical data), wanting to find the top node (root).
从树中某处的一个节点（行）（分层数据）开始，想要找到顶部节点（根）。
The query is taking advantage of the fact that the ONLYthe top node (root) of a tree don't have a parent, which is a very common attribute of the top node (root) in any tree structure.
该查询利用了这样一个事实，即只有树的顶部节点（根）没有父节点，这是任何树结构中顶部节点（根）的一个非常常见的属性。
SELECT
c.id
FROM
node c
WHERE
c.parent_id is null
CONNECT BY PRIOR
c.parent_id = c.id
START WITH
c.id = 1059002615
回答by jofelanca
SELECT * FROM (
SELECT CONNECT_BY_ROOT myTable.ID AS "Top Level ID"
FROM myTable
CONNECT BY PRIOR myTable.ID = myTable.ParentID
)
WHERE myTable.ParentID IS NULL
