需求:统计上级部门的销售额,制造数据如下:。
创建表:
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;
统计所有父部门的采集数量。
思路1:先将所有的父部门查出来,然后弄一个子查询做递归查询出其子节点,然后求和,这种思路很简单,效率也低,不推荐。
思路2:先用层次查询查出树形结构,要想统计一个节点下的汇总,需要用到connect_by_root,同一个节点下的connect_by_root是一样的,然后再group by。
-- 生成sql段
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
)
select root_id,sum(sale_num) s_sum from temp group by root_id
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
本文转载至:https://blog.csdn.net/stevendbaguo/article/details/44344639?d=1537869102349 详情请参考原文。