我的表格结构是
| 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.
更多推荐
在oracle中查找父级(任何级别)的最多子级节点
发布评论