使用 CONNECT BY 通过 Oracle 中的 SQL 查询获取 Hierarchy 中的所有父级和一个子级
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2551207/
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
Using CONNECT BY to get all parents and one child in Hierarchy through SQL query in Oracle
提问by s khan
I was going through some previous posts on CONNECT BY usage. What I need to find is that what to do if I want to get all the parents (i.e, up to root) and just one child for a node, say 4.
我正在浏览以前关于 CONNECT BY 用法的一些帖子。我需要找到的是，如果我想获得所有父母（即，最多根）和一个节点的一个孩子，例如 4，该怎么办。
It seems Like I will have to use union of the following two:-
似乎我将不得不使用以下两个的并集：-
SELECT *
FROM hierarchy
START WITH id = 4
CONNECT BY id = PRIOR parent
union
SELECT *
FROM hierarchy
WHERE LEVEL =<2
START WITH
id = 4
CONNECT BY
parent = PRIOR id
Is there a better way to do this, some workaround that is more optimized?
有没有更好的方法来做到这一点，一些更优化的解决方法？
回答by Peter Lang
You should be able to do this using a sub-select (and DISTINCT) to find all children of 4:
您应该能够使用子选择（和DISTINCT）来查找以下所有子项4：
Select Distinct *
From hierarchy
Start With id In ( Select id
From hierarchy
Where parent = 4 )
Connect By id = Prior parent
Using UNIONyou could at least remove the CONNECT BYfrom your second query:
使用UNION你至少可以CONNECT BY从你的第二个查询中删除：
Select *
From hierarchy
Start With id = 4
Connect By id = Prior parent
Union
Select *
From hierarchy
Where parent = 4
Never use SELECT *, always name the columns you actually need. This makes your query easier to read, to maintain and to optimize.
永远不要使用SELECT *，始终命名您实际需要的列。这使您的查询更易于阅读、维护和优化。
