如何获取Oracle树层次结构中所有父项的子项总数? [英] How to obtain total children count for all parents in Oracle tree hierarchy?

问题描述

我有一个Oracle Tree层次结构，该结构基本上类似于称为MY_TABLE的下表

I have an Oracle Tree hierarchy structure that is basically similar to the following table called MY_TABLE

(LINK_ID,
 PARENT_LINK_ID,
 STEP_ID )

在MY_TABLE中包含以下示例数据:

with the following sample data within MY_TABLE:

LINK_ID     PARENT_LINK_ID      STEP_ID
-----------------------------------------------
A           NULL                0
B           NULL                0
AA          A                   1
AB          A                   1
AAA         AA                  2
BB          B                   1
BBB         BB                  2
BBBA        BBB                 3
BBBB        BBB                 3

基于上述示例数据，我需要生成一个报告，该报告基本上返回所有子项的总行数 的两个父链接ID(仅顶层)，也就是说，我需要生成一个SQL查询，该查询返回以下信息，即:

Based on the above sample data, I need to produce a report that basically returns the total count of rows for all children of both parent link IDs (top level only required), that is, I need to produce a SQL query that returns the following information, i.e.:

PARENT  RESULT COUNT
----------------------------
A       3
B       4   

因此，我需要汇总属于所有(父)链接ID的子级总数，其中LINK_ID的PARENT_LINK_ID为NULL

So I need to rollup total children that belong to all (parent) link ids, where the LINK_IDs have a PARENT_LINK_ID of NULL

推荐答案

我认为是这样的:

select link, count(*)-1 as "RESULT COUNT"
  from (
    select connect_by_root(link_id) link
    from my_table
    connect by nocycle parent_link_id = prior link_id
    start with parent_link_id is null)
group by link
order by 1 asc

这篇关于如何获取Oracle树层次结构中所有父项的子项总数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！