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