Oracle数据库入门教程 层次树统计
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;
评论暂时关闭