从一个节点上获取所有的最后一个级别的子(页)(hierarhical查询Oracle 11G)

I am trying and searching the way to get ALL last level children (leafs) from a node, in a hierchical query in Oracle 11g database.

在Oracle 11g数据库中，我正在尝试并寻找从一个节点获得所有最后一个级别的子(叶子)的方法。

I have 2 tables: "Nodes" (A list of all nodes with their respective value), and "Relation" which specify father-child relation:

我有两个表:“节点”(所有节点的列表都有各自的值)，“关系”指定父子关系:

--NODES--

 ID_NODE    -      VALUE
1       3
2       6
3       9
4       2
5       4
6       5
7       2
8       7
9       8
10      1

--RELATION--

ID_FATHER    -   ID_CHILD
1       2
1       3
1       4
2       5
2       6
4       7
5       8
5       9
7       10

I have read about CONNECT_BY_ISLEAF which returns 1 if it is a leaf, but I cannot query CONNECT_BY_ISLEAF like the Oracle example, and I don´t get any result. Even though I don´t know exactly how to make the query exactly with this function (Using case condition for example?)

我读过关于CONNECT_BY_ISLEAF返回1如果是一片叶子,但我不能查询CONNECT_BY_ISLEAF像Oracle的例子,我也´t得到任何结果。´虽然我不知道如何使用这个函数使查询准确(使用条件例?)

Thank you too much!

谢谢你太多!

2 个解决方案

#1

I think, something like that should do the trick:

我想，应该是这样的:

SELECT * FROM
(SELECT n.id, n.val, CONNECT_BY_ISLEAF isleaf FROM NODES n 
       LEFT JOIN RELATION r ON n.id = r.id_child
CONNECT BY PRIOR n.id = r.id_father
START WITH r.id_father IS NULL)
WHERE isleaf = 1

Oh, and by the way, you can get all leafs without even using hierahical query. Just select all nodes, which are not father's node for any node from relation table. Something like that:

哦，顺便说一下，你甚至可以不用层次查询就能得到所有的叶子。只要选择所有节点，这些节点不是来自关系表的任何节点的父节点。这样的:

SELECT n.* FROM NODES n
WHERE NOT EXISTS (SELECT ID_FATHER FROM RELATION r
                  WHERE r.id_father = n.id)

In order to get the leaf nodes from the specified node, just change condition in START WITH clause, to start tree reverse from the node you're interested in. For example, this query will return you all children leafs of node with id = 5:

为了从指定节点获取叶子节点，只需在START WITH子句中改变条件，从您感兴趣的节点开始树倒。例如，该查询将以id = 5返回节点的所有子节点:

SELECT * FROM
(SELECT n.id, n.val, CONNECT_BY_ISLEAF isleaf FROM NODES n 
       LEFT JOIN RELATION r ON n.id = r.id_child
CONNECT BY PRIOR n.id = r.id_father
START WITH n.id = 5)
WHERE isleaf = 1

#2

You can simply use CONNECT_BY_ISLEAF.

您可以简单地使用CONNECT_BY_ISLEAF。

SELECT n.id, n.val
FROM NODES n 
LEFT JOIN RELATION r ON (n.id = r.id_child)
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR n.id = r.id_father
START WITH r.id_father IS NULL

#1