加载中…Oracle 层次树统计 汇总
(2012-10-20 18:01:14)
标签：
杂谈 |
统计部门的员工个数（员工个数=本部门人数+下级部门人数）
部门表：
http://dl.iteye.com/upload/attachment/242895/68da1240-04d9-388a-9ebd-b110c60f4dbd.jpg层次树统计汇总" TITLE="Oracle 层次树统计 汇总" />
员工表：
http://dl.iteye.com/upload/attachment/242897/66a6a769-9c4a-3e2a-8cd4-6ff5d558d6fc.jpg层次树统计汇总" TITLE="Oracle 层次树统计 汇总" />
sql 语句：
查询结果：
http://dl.iteye.com/upload/attachment/242900/e38af876-12ed-3f9e-8d77-7cb5d67d8ab2.jpg层次树统计汇总" TITLE="Oracle 层次树统计 汇总" />
oracle10g sql语句：
部门表：
http://dl.iteye.com/upload/attachment/242895/68da1240-04d9-388a-9ebd-b110c60f4dbd.jpg层次树统计
员工表：
http://dl.iteye.com/upload/attachment/242897/66a6a769-9c4a-3e2a-8cd4-6ff5d558d6fc.jpg层次树统计
sql 语句：
Sql代码 http://chzhxu.iteye.com/images/icon_star.png层次树统计 汇总" />
-
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, ' ' )||idas id,lpad(' ' ,4 * levs, ' ' )||deptnameas 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
查询结果：
http://dl.iteye.com/upload/attachment/242900/e38af876-12ed-3f9e-8d77-7cb5d67d8ab2.jpg层次树统计
oracle10g sql语句：
Sql代码 http://chzhxu.iteye.com/images/icon_star.png层次树统计 汇总" />
-
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;
喜欢
0
赠金笔
前一篇：js查看Object对象的内容