如何计算来自Oracle树形层次结构的所有父项的总子项计数?

huangapple go评论72阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年6月12日 21:41:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/76457266.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定