英文:
How to calculate total children count for all parents from a Oracle tree hierarchy?
问题
我有一个类似于以下表格的Oracle树形层次结构,称为Hierarchy_Tree:
(PARENT, CHILD)
在Hierarchy_Tree中包含以下示例数据:
CHILD PARENT
-------------------------
AA A
AB A
AAA AA
BB B
BBB BB
BBBA BBB
BBBB BBB
C1 C
C2 C
C3 C
C4 C3
C5 C3
C6 C3
C7 C6
C8 C6
根据上述示例,我需要生成一个SQL查询,仅返回所有父级(仅需要顶级),即:
PARENT COUNT
----------------------------
A 3
B 4
C 8
因此,我需要汇总属于所有PARENT的子项。
我尝试了类似于以下的查询,但没有成功:
select child1, count(*)-1 as "RESULT COUNT"
from (
select connect_by_root(child) child1
from Hierarchy_Tree
connect by nocycle parent = prior child
)
group by child1
order 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 A
AB A
AAA AA
BB B
BBB BB
BBBA BBB
BBBB BBB
C1 C
C2 C
C3 C
C4 C3
C5 C3
C6 C3
C7 C6
C8 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 3
B 4
C 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) child1
from Hierarchy_Tree
connect by nocycle parent = prior child
)
group by child1
order 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_Tree
start with parent in (select parent from Hierarchy_Tree d1
where not exists(select 1 from Hierarchy_Tree d2 where d2.child = d1.parent)
)
connect by parent = prior child
)
group by rootp
;
A 3
B 4
C 8
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论