从节点获取所有最后一级的子级(叶)(层次查询Oracle 11G) [英] get ALL last level children (leafs) from a node (hierarhical queries Oracle 11G)

问题描述

我正在尝试在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.

我有2个表:节点"(具有各自值的所有节点的列表)和关系"，用于指定父子关系:

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

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

---

-关系-

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

我已阅读有关CONNECT_BY_ISLEAF的信息，如果它是叶子则返回1，但是我无法像Oracle示例一样查询CONNECT_BY_ISLEAF，但没有得到任何结果.即使我不知道该如何精确地使用此功能进行查询(例如，使用大小写条件?)

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?)

非常感谢您！

推荐答案

我认为，类似的方法应该可以解决问题:

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)

---

为了从指定的节点获取叶节点，只需更改START WITH子句中的条件，以从您感兴趣的节点开始反向树.例如，此查询将返回具有以下内容的节点的所有子叶: id = 5:

---

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:

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

这篇关于从节点获取所有最后一级的子级(叶)(层次查询Oracle 11G)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！