从节点获取所有最后一级子节点(叶子)(分层查询 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，并且我没有得到任何结果.即使我不知道如何准确地使用此函数进行查询(例如使用 case 条件?)

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屋！