统计部门的员工个数(员工个数=本部门人数+下级部门人数)

部门表：

员工表：

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;