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行。