Oracle树形汇总--connect_by_root

Oracle树形汇总--connect_by_root

有个需求：统计上级部门的销售额，制造数据如下：

drop table dept;
create table dept
(
DEPTNO number,
DEPTNAME varchar2(50),
PARENT_DEPTNO number
);
insert into dept values(1,'市场部',-1);
insert into dept values(2,'市场一部',1);
insert into dept values(3,'市场二部',1);
insert into dept values(4,'销售一组',2);
insert into dept values(5,'销售二组',2);
insert into dept values(6,'销售三组',3);
insert into dept values(7,'销售四组',3);
insert into dept values(8,'电力1',4);
insert into dept values(9,'电力2',5);
insert into dept values(10,'电力3',4);
insert into dept values(11,'电力4',5);
insert into dept values(12,'石油1',6);
insert into dept values(13,'石油2',7);
insert into dept values(14,'石油3',6);
insert into dept values(15,'石油4',7);
commit;
drop table sales;
create table sales
(
id number,
sale_num number,
deptno number
);
insert into sales values(1,50000,8);
insert into sales values(2,10000,9);
insert into sales values(3,60000,10);
insert into sales values(4,10000,11);
insert into sales values(5,20000,12);
insert into sales values(6,40000,13);
insert into sales values(7,90000,14);
insert into sales values(8,110000,15);
commit;

select dd.deptno, dd.deptname, nvl(s.sale_num, 0) sale_num, dd.parent_deptno
from (select d.deptno,
cast(lpad(' ', level * 2 - 1) || d.deptname as varchar2(50)) deptname,
rownum rn,
d.parent_deptno
from dept d
start with d.PARENT_DEPTNO = -1
connect by prior d.DEPTNO = d.PARENT_DEPTNO) dd,
sales s
where dd.deptno = s.deptno(+)
order by dd.rn;

DEPTNO DEPTNAME SALE_NUM PARENT_DEPTNO
-------- -------------------------------------------------- ---------- -------------

1 市场部 0 -1
2 市场一部 0 1
4 销售一组 0 2
8 电力1 50000 4
10 电力3 60000 4
5 销售二组 0 2
9 电力2 10000 5
11 电力4 10000 5
3 市场二部 0 1
6 销售三组 0 3
12 石油1 20000 6
14 石油3 90000 6
7 销售四组 0 3
13 石油2 40000 7
15 石油4 110000 7

统计所有父部门的营业额。

思路1：先将所有的父部门查出来，然后弄一个子查询做递归查询出其子节点，然后求和，这种思路很简单，效率也低，不推荐。

思路2：先用层次查询查出树形结构，要想统计一个节点下的汇总，需要用到connect_by_root，同一个节点下的connect_by_root是一样的，然后再group by。

--实现如下

with temp as
(select dd.deptno,nvl(s.sale_num, 0) sale_num,root_id,rn
from (select d.deptno,
connect_by_root(d.deptno) root_id,
rownum rn
from dept d
start with d.PARENT_DEPTNO in(select PARENT_DEPTNO from dept)
connect by prior d.DEPTNO = d.PARENT_DEPTNO) dd,
sales s
where dd.deptno = s.deptno
order by dd.rn),
temp1 as(select root_id,sum(sale_num) s_sum from temp group by root_id),
temp3 as( --加上temp3纯粹是为了阅读方便
select d.deptno,
cast(lpad(' ', level * 2 - 1) || d.deptname as varchar2(50)) deptname,
rownum rn
from dept d
start with d.PARENT_DEPTNO = -1 connect by prior d.DEPTNO = d.PARENT_DEPTNO
)
select t3.deptno,t3.deptname,t1.s_sum from temp1 t1,temp3 t3 where t1.root_id = t3.deptno
order by rn;

DEPTNO DEPTNAME S_SUM

---------- -------------------------------------------------- ----------
1 市场部 390000
2 市场一部 130000
4 销售一组 110000
8 电力1 50000
10 电力3 60000
5 销售二组 20000
9 电力2 10000
11 电力4 10000
3 市场二部 260000
6 销售三组 110000
12 石油1 20000
14 石油3 90000
7 销售四组 150000
13 石油2 40000
15 石油4 110000
已选择15行。