oracle 层次树统计

统计部门的员工个数（员工个数=本部门人数+下级部门人数）

部门表：


员工表：




sql 语句：

with temp as(   
  select t1.deptid as id, t1.supdeptid parent, num, level levs,t1.deptname   
    from dept t1   
    left join (select deptid, count(t.deptid) num   
                 from emp t   
                group by t.deptid) t2 on t1.deptid = t2.deptid   
   start with t1.supdeptid is null
  connect by prior t1.deptid = t1.supdeptid)   
    select lpad(' ', 4 * levs, ' ')||id as id,lpad(' ', 4 * levs, ' ')||deptname as deptname,   
           (select nvl(num, 0) from temp where id = t.id) +   
           (select nvl(sum(num), 0)   
              from temp  
            connect by parent = prior id   
             start with parent = t.id) cnt   
      from temp t 

查询结果：




oracle10g sql语句：

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;