在oracle中查找父级(任何级别)的最多子级节点

本文介绍了在oracle中查找父级(任何级别)的最多子级节点的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的表格结构是

| Parent ID | ID | |-------------|-------------| | a | b | | b | c | | b | d | | b | e | | c | f | | d | g | | e | h |

我想为所有父节点获得最多的子节点. 在另一个表中，我有a，b和c.然后我想得到以下结果.

| Parent_ID | Child | |-------------|-------------| | a | f | | a | g | | a | h | | b | f | | b | g | | b | h | | c | f |

f，g，h是最低的颗粒.

解决方案

最子节点"是指树的叶子节点.您可以使用层次结构(CONNECT BY)查询的CONNECT_BY_ISLEAF伪列来确定叶节点.

给出表中的样本数据，以下查询将产生所需的结果:

select connect_by_root id id , parent_did from table1 where connect_by_isleaf = 1 connect by id = prior parent_did start with id in ('a','b','c'); | ID | PARENT_DID | |----|------------| | a | f | | a | g | | a | h | | b | f | | b | g | | b | h | | c | f |

SQL提琴

考虑到更新的数据和要求，包括第二个表包含a，b和c作为开始条件的事实:

select connect_by_root parent_id parent_id , id from table1 where connect_by_isleaf = 1 connect by prior id = parent_id start with parent_id in (select id from table2) | PARENT_ID | ID | |-----------|----| | a | f | | a | g | | a | h | | b | f | | b | g | | b | h | | c | f |

SQL小提琴 您可以从文档中了解有关分层查询的更多信息.. >

My Table structure is

| Parent ID | ID | |-------------|-------------| | a | b | | b | c | | b | d | | b | e | | c | f | | d | g | | e | h |

I want to get the most child node for all parent . In a other table I have a , b and c . Then I want to get the below result .

| Parent_ID | Child | |-------------|-------------| | a | f | | a | g | | a | h | | b | f | | b | g | | b | h | | c | f |

f , g , h are the lowest granular .

解决方案

By "most child node" I expect you mean the leaf nodes of the tree. You can determine the leaf nodes with the CONNECT_BY_ISLEAF pseudo column of a hierarchical (CONNECT BY) query.

Given your sample data in a table the following query yields the desired results:

select connect_by_root id id , parent_did from table1 where connect_by_isleaf = 1 connect by id = prior parent_did start with id in ('a','b','c'); | ID | PARENT_DID | |----|------------| | a | f | | a | g | | a | h | | b | f | | b | g | | b | h | | c | f |

SQL Fiddle

Taking your updated data and requirements into account including the fact that a second table holds a, b, and c as the start conditions:

select connect_by_root parent_id parent_id , id from table1 where connect_by_isleaf = 1 connect by prior id = parent_id start with parent_id in (select id from table2) | PARENT_ID | ID | |-----------|----| | a | f | | a | g | | a | h | | b | f | | b | g | | b | h | | c | f |

SQL Fiddle You can learn more about hierarchical queries from the documentation.