oracle 树状结构数据库,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