查询部门树下，各级部门人员数量

部门表：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;