部门表：dept 部门id：deptid ，父级部门id：supdepid
用户表：emp 部门id：deptid
connect_by_root：父级id
select a.root as id, nvl(sum(b.num), 0) num
from (select id, fid, connect_by_root(id) root
from (select d.deptid as id, d.supdepid as fid from dept d start with d.supdepid is null
connect by prior d.deptid = d.supdepid)
connect by prior id = fid) a
left join (select deptid, count(t.deptid) num from emp t group by t.deptid) b on a.id =
b.deptid
group by root
order by root;