如何解决Oracle层级查询返回重复记录问题？

问题背景 #

在Oracle中使用connect by进行层级查询时，出现父记录对应重复子记录的问题。以下是示例场景：

测试数据准备 #

drop table t1 purge;

create table t1(en varchar2(10),bug number, mgr varchar2(10));

insert into t1 values('a',101,'z');
insert into t1 values('a',102,'z');
insert into t1 values('a',103,'z');
insert into t1 values('a',104,'z');
insert into t1 values('b',201,'a');
insert into t1 values('b',202,'a');
insert into t1 values('b',203,'a');
insert into t1 values('c',301,'z');
insert into t1 values('c',302,'z');
insert into t1 values('c',303,'z');
commit;

原始查询及问题 #

执行以下层级查询：

select en, bug, level from t1
start with mgr='z'
connect by prior en=mgr;

返回结果中，b的bug记录重复出现4次（对应a的4条bug记录），不符合预期。

预期输出（单bug层级展示） #

希望基于en和mgr的层级关系，展示每个唯一bug的层级，无重复：

EN  BUG LEVEL
a   101 1
a   102 1
a   103 1
c   301 1
c   302 1
c   303 1
b   201 2
b   203 2
b   202 2

问题原因 #

原始查询直接基于表中每条记录进行层级遍历：每个a的bug记录都会作为父节点，触发一次对所有b记录的关联，导致b的记录被重复遍历多次。

解决方案1：单bug层级展示 #

先提取唯一的en-mgr层级关系，再关联原表获取每个bug的层级：

select t.en, t.bug, l.emp_level as level
from t1 t
join (
    -- 获取每个员工的层级
    select en, level as emp_level
    from (select distinct en, mgr from t1)
    start with mgr='z'
    connect by prior en = mgr
) l on t.en = l.en
order by level, en, bug;

更新1：按员工分组统计bug数量 #

需求改为按员工分组，统计每个员工的bug总数并展示层级，预期输出：

EN  BUG_COUNT   LEVEL
a   4           1
c   3           1
b   3           2

解决方案 #

先分组统计每个员工的bug数，再关联层级信息：

select t.en, t.bug_count, l.emp_level as level
from (
    -- 统计每个员工的bug数量
    select en, count(bug) as bug_count
    from t1
    group by en
) t
join (
    -- 获取每个员工的层级
    select en, level as emp_level
    from (select distinct en, mgr from t1)
    start with mgr='z'
    connect by prior en = mgr
) l on t.en = l.en
order by level, en;

更新2：按员工和经理层级分组统计 #

需求进一步细化，按经理-员工层级分组，展示员工bug数、累计bug数（含自身及所有子节点），预期输出：

MGR  EN   EN_BUG_COUNT  CUMULATIVE_BUG_COUNT    LEVEL
z    null         null                    10        0
z    a               4                     7        1
a    b               3                     3        2
z    c               3                     3        1

解决方案 #

使用递归CTE实现层级累计统计：

with emp_bugs as (
    -- 先统计每个员工的bug总数
    select en, mgr, count(bug) as bug_count
    from t1
    group by en, mgr
),
hierarchy as (
    -- 先处理叶子节点（无下属的员工）
    select 
        mgr,
        en,
        bug_count as en_bug_count,
        bug_count as cumulative_bug_count,
        1 as level
    from emp_bugs eb
    where not exists (select 1 from emp_bugs eb2 where eb2.mgr = eb.en)
    union all
    -- 递归处理非叶子节点，累计自身bug数+子节点累计数
    select 
        eb.mgr,
        eb.en,
        eb.bug_count as en_bug_count,
        eb.bug_count + h.cumulative_bug_count as cumulative_bug_count,
        h.level + 1 as level
    from emp_bugs eb
    join hierarchy h on eb.en = h.mgr
),
top_level as (
    -- 添加顶级经理z的总累计记录
    select 
        'z' as mgr,
        null as en,
        null as en_bug_count,
        sum(en_bug_count) as cumulative_bug_count,
        0 as level
    from hierarchy
)
-- 合并所有结果并排序
select * from top_level
union all
select mgr, en, en_bug_count, cumulative_bug_count, level from hierarchy
order by level, mgr, en nulls last;

内容的提问来源于stack exchange，提问作者bprasanna