Oracle树形汇总--connect_by_root

迈不过友情╰ 2022-08-07 05:56 103阅读 0赞

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

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