oralce里面类似这样，你也没有说明白关联逻辑，我只是按你模拟的把结果弄出来了。其它的数据库也可以用，只是mysql低于8的版本不能用with这种语法

with tmp as (

select 'A' as p_dept_id,'AA' as dept_id,2 as num from dual union all

select 'A' as p_dept_id,'AB' as AA,3 as num from dual union all

select 'A' as p_dept_id,'AC' as AB,4 as num from dual union all

select 'AA' as p_dept_id,'AAA' as AC,1 as num from dual union all

select 'AA' as p_dept_id,'AAB' as AAA,1 as num from dual union all

select 'AA' as p_dept_id,'AAC' as AAB,1 as num from dual

)

select t.p_dept_id,t.dept_id,sum(t.my_num) as my_num from (

 select 

   a.p_dept_id,

   a.dept_id,

   b.num as my_num

   from tmp a

join tmp b on a.dept_id=b.p_dept_id

where length(a.p_dept_id)=1

union all

 select 

   a.p_dept_id,

   a.dept_id,

   a.num as my_um

   from tmp a

where length(a.p_dept_id)=1

) t

group by t.p_dept_id,t.dept_id

把num列的左父格设置为ID列，num列汇总求和