Oracle樹形匯總--connect_by_root，oracleconnectby

Oracle樹形匯總--connect_by_root，oracleconnectby

    有個需求：統計上層業務的銷售額，製造資料如下：

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