Oracle 层次树统计 汇总

刘亚东

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

部门表：
层次树统计 汇总" title="Oracle 层次树统计 汇总" action-data="http%3A%2F%2Fdl.iteye.com%2Fupload%2Fattachment%2F242895%2F68da1240-04d9-388a-9ebd-b110c60f4dbd.jpg" action-type="show-slide" style="border: 0px; list-style: none; color: rgb(73, 73, 73); font-family: Helvetica, Tahoma, Arial, sans-serif; line-height: 25.2px;">

员工表：

层次树统计 汇总" title="Oracle 层次树统计 汇总" action-data="http%3A%2F%2Fdl.iteye.com%2Fupload%2Fattachment%2F242897%2F66a6a769-9c4a-3e2a-8cd4-6ff5d558d6fc.jpg" action-type="show-slide" style="border: 0px; list-style: none; color: rgb(73, 73, 73); font-family: Helvetica, Tahoma, Arial, sans-serif; line-height: 25.2px;">


sql 语句：

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   
  

查询结果：


层次树统计 汇总" title="Oracle 层次树统计 汇总" action-data="http%3A%2F%2Fdl.iteye.com%2Fupload%2Fattachment%2F242900%2Fe38af876-12ed-3f9e-8d77-7cb5d67d8ab2.jpg" action-type="show-slide" style="border: 0px; list-style: none; color: rgb(73, 73, 73); font-family: Helvetica, Tahoma, Arial, sans-serif; line-height: 25.2px;">

oracle10g sql语句：
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;  
  

刘亚东

张兴康 发表于 2016-1-7 19:09
不错，顶一个