Oracle 分层查询：如何包含顶级父级
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/893118/
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
Oracle Hierarchical query: how to include top-level parent
提问by chris
I have a hierarchical query to track a reporting structure. This almost works, except that it's not reporting the very top level node, probably because the top-level people "report" to themselves.
我有一个分层查询来跟踪报告结构。这几乎有效，只是它没有报告最顶层的节点，可能是因为顶层人员向自己“报告”。
The query is:
查询是：
select
level,
empid,
parentid
from usertable
connect by nocycle prior parentid= empid
start with empid = 50
This produces:
这产生：
LEVEL EMPID PARENTID
------ ----- --------
1 50 258
2 258 9555
3 9555 17839
I'm not getting a level 4, since it would look like:
我没有达到 4 级，因为它看起来像：
4 17839 17839
Without changing data, is there a way to modify my query so that all 4 levels are returned? The goal is to get the empids, so I can do a check for
在不更改数据的情况下，有没有办法修改我的查询以便返回所有 4 个级别？目标是获得 empids，所以我可以检查
id in (hierarchical subquery)
id in (hierarchical subquery)
BTW, if I remove the nocycle from the query I get an error.
顺便说一句，如果我从查询中删除 nocycle，我会收到一个错误。
回答by Rob van Wijk
Chris,
克里斯，
You only get 3 rows because your top level row is not set the way it should to handle hierarchical queries. Typically the top level row, or president KING in Oracle's well known EMP table, has no manager. In your case you should not set the parentid of 17389 to 17389 itself, but to NULL. Either update the table accordingly, or use a view to accomodate for this situation.
您只能获得 3 行，因为您的顶级行没有按照处理分层查询的方式进行设置。通常，Oracle 著名的 EMP 表中的顶级行或总裁 KING 没有经理。在您的情况下，您不应将 17389 的 parentid 设置为 17389 本身，而应设置为 NULL。相应地更新表，或使用视图来适应这种情况。
An example:
一个例子：
SQL> select empno
2 , mgr
3 from emp
4 where empno in (7876,7788,7566,7839)
5 /
EMPNO MGR
---------- ----------
7566 7839
7788 7566
7839 7839
7876 7788
4 rijen zijn geselecteerd.
This part of the EMP table has four levels with its top level row (7839) set to itself. The same as your empid 17839. And this leads to only three rows using your query:
EMP 表的这部分有四个级别，其顶级行 (7839) 设置为自身。与您的 empid 17839 相同。这仅导致使用您的查询的三行：
SQL> select level
2 , empno
3 , mgr
4 from emp
5 connect by nocycle prior mgr = empno
6 start with empno = 7876
7 /
LEVEL EMPNO MGR
---------- ---------- ----------
1 7876 7788
2 7788 7566
3 7566 7839
3 rijen zijn geselecteerd.
Either use a (inline) view to set the mgr/parentid column to null for the top level:
要么使用（内联）视图将 mgr/parentid 列设置为顶级的 null：
SQL> select level
2 , empno
3 , mgr
4 from ( select empno
5 , nullif(mgr,empno) mgr
6 from emp
7 )
8 connect by nocycle prior mgr = empno
9 start with empno = 7876
10 /
LEVEL EMPNO MGR
---------- ---------- ----------
1 7876 7788
2 7788 7566
3 7566 7839
4 7839
4 rijen zijn geselecteerd.
Or fix your data with an UPDATE statement:
或者使用 UPDATE 语句修复您的数据：
SQL> update emp
2 set mgr = null
3 where empno = 7839
4 /
1 rij is bijgewerkt.
SQL> select level
2 , empno
3 , mgr
4 from emp
5 connect by nocycle prior mgr = empno
6 start with empno = 7876
7 /
LEVEL EMPNO MGR
---------- ---------- ----------
1 7876 7788
2 7788 7566
3 7566 7839
4 7839
4 rijen zijn geselecteerd.
And you can leave out the NOCYCLE keyword as well, after you are done fixing.
完成修复后，您也可以省略 NOCYCLE 关键字。
Regards, Rob.
问候，罗布。
回答by Matthew Watson
You need to do the hierarchy the other way around, from the root to the leaves.
您需要以相反的方式进行层次结构，从根到叶。
select
level,
empid,
parentid
from usertable
start with empid = 17839
connect by empid != 17839 and prior empid = parentid
LEVEL EMPID PARENTID
---------------------- ---------------------- ----------------------
1 17839 17839
2 9555 17839
3 258 9555
4 50 258
4 rows selected
回答by Van Heddegem Roeland
You don't have to change your structure.
你不必改变你的结构。
just use the following query
只需使用以下查询
select
level,
empid,
parentid
from usertable
connect by prior parentid = empid
AND parentid <> empid -- This line prohibits cycling and ALLOWS a row where parentid = empid
start with empid = 50
回答by Clark
Van Heddegem Roeland's answer doesn't work for me, I had already tried that, but I have managed to do it without an inline view, in the connect clause, by adding:-
Van Heddegem Roeland 的回答对我不起作用，我已经尝试过了，但是我在 connect 子句中通过添加以下内容设法在没有内联视图的情况下做到了：-
and prior empid <> parentid
The following post explains why that works - if you can get your head round it! Although it does make sound logical sense once you do 'get it'. (It's to do with the order of evaluation of each side of the <> operator.)
下面的帖子解释了为什么会这样——如果你能理解的话！尽管一旦你“明白了”，它确实具有合理的逻辑意义。（这与 <> 运算符每一侧的计算顺序有关。）
Oracle: Connect By Loop in user data
The inline view will work but without research on your particular dataset, I don't know what impact an inline view might have on the query path. Adding the extra clause is probably the 'correct' way to do it in most situations, IMHO.
内联视图可以工作，但如果不研究您的特定数据集，我不知道内联视图可能对查询路径产生什么影响。恕我直言，在大多数情况下，添加额外的子句可能是“正确”的方法。
回答by san
seems like you have a cycle in data. Without "nocycle" it will not work straight away. If you know that all your data has maximum nesting level 4, then you could add condition "and level <= 4" and remove nocycle. Should work.
好像你有一个数据周期。没有“nocycle”，它不会立即起作用。如果您知道所有数据的最大嵌套级别为 4，那么您可以添加条件“and level <= 4”并删除 nocycle。应该管用。
