玉祥平台客服-15087858732

标题: Oracle 层次树统计汇总 [打印本页]

作者: 万望 时间: 2020-2-28 22:40
长见识~\(≧▽≦)/~啦啦啦

作者: 刘亚东 时间: 2020-6-7 09:35
标题: 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;

作者: 张军勇 时间: 2020-6-7 09:38
用于html5显示树型还是有一定的帮助。

作者: 陈晓龙 时间: 2020-6-7 14:03
果然牛逼！

作者: 王爱东 时间: 2020-6-7 15:16
一条sql搞定，很不错哈。

作者: 张兴康 时间: 2020-6-7 19:09
不错，顶一个

作者: 刘亚东 时间: 2020-6-8 09:33

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

作者: 张兴康 时间: 2020-6-8 17:01

刘亚东发表于 2016-1-8 09:33

欢迎光临玉祥平台客服-15087858732 (http://p.delit.cn/)