英文:
How to calculate total children count for all parents from a Oracle tree hierarchy?
问题
我有一个类似于以下表格的Oracle树形层次结构，称为Hierarchy_Tree：
(PARENT, CHILD)
在Hierarchy_Tree中包含以下示例数据：
CHILD PARENT-------------------------AA AAB AAAA AABB BBBB BBBBBA BBBBBBB BBBC1 CC2 CC3 CC4 C3C5 C3C6 C3C7 C6C8 C6
根据上述示例，我需要生成一个SQL查询，仅返回所有父级（仅需要顶级），即：
PARENT COUNT----------------------------A 3B 4C 8
因此，我需要汇总属于所有PARENT的子项。
我尝试了类似于以下的查询，但没有成功：
select child1, count(*)-1 as "RESULT COUNT"from (select connect_by_root(child) child1from Hierarchy_Treeconnect by nocycle parent = prior child)group by child1order by 1 asc
英文:
I have an Oracle Tree hierarchy structure that is similar to the following table called Hierarchy_Tree
(PARENT, CHILD)
with the following sample data within Hierarchy_Tree:
CHILD PARENT-------------------------AA AAB AAAA AABB BBBB BBBBBA BBBBBBB BBBC1 CC2 CC3 CC4 C3C5 C3C6 C3C7 C6C8 C6
Based on the above sample all parent (top level only required), that is, I need to produce a SQL query that returns the following information, i.e.:
PARENT COUNT----------------------------A 3B 4C 8
So I need to rollup total children that belong to all PARENT
I tried something liked this, but it didn't work.
select child1, count(*)-1 as "RESULT COUNT"from (select connect_by_root(child) child1from Hierarchy_Treeconnect by nocycle parent = prior child)group by child1order by 1 asc
答案1
得分: 1
select rootp as 父级, count(*) as 计数 from (
select 子级, 父级, connect_by_root 父级 as rootp from 层次树
start with 父级 in (select 父级 from 层次树 d1
where not exists(select 1 from 层次树 d2 where d2.子级 = d1.父级)
)
connect by 父级 = prior 子级
)
group by rootp
;
英文:
select rootp as parent, count(*) as count from (select child, parent, connect_by_root parent as rootp from Hierarchy_Treestart with parent in (select parent from Hierarchy_Tree d1where not exists(select 1 from Hierarchy_Tree d2 where d2.child = d1.parent))connect by parent = prior child)group by rootp;A 3B 4C 8
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论