Oracle tree summary -- connect_by_root

Oracle tree summary -- connect_by_root

There is a requirement: Count the sales volume of the higher-level department. The manufacturing data is as follows:

Drop table dept; create table dept (DEPTNO number, DEPTNAME varchar2 (50), PARENT_DEPTNO number); insert into dept values (1, 'marketing Department ',-1 ); insert into dept values (2, 'market parts', 1); insert into dept values (3, 'market parts', 1); insert into dept values (4, 'sale group ', 2); insert into dept values (5, 'sale group 2', 2); insert into dept values (6, 'sale group 3 ', 3); insert into dept values (7, 'sale group', 3); insert into dept values (8, 'power 1', 4 ); insert into dept values (9, 'power 2', 5); insert into dept values (10, 'power 3', 4); insert into dept values (11, 'Power 4', 5); insert into dept values (12, 'oil 1', 6); insert into dept values (13, 'oil 2', 7 ); insert into dept values (14, 'oil 3', 6); insert into dept values (15, 'oil 4', 7); commit; drop table sales; create table sales (id number, sale_num number, deptno number); insert into sales values (, 8); insert into sales values (, 9 ); insert into sales values (, 10); insert into sales values (, 11); insert into sales values (, 12); insert into sales values (, 13 ); insert into sales values (, 14); insert into sales values (, 15); commit; select dd. deptno, dd. deptname, nvl (s. sale_num, 0) sale_num, dd. parent_deptnofrom (select d. deptno, cast (lpad ('', level * 2-1) | d. deptname as varchar2 (50) deptname, rownum rn, d. parent_deptnofrom dept dstart with d. PARENT_DEPTNO =-1 connect by prior d. DEPTNO = d. PARENT_DEPTNO) dd, sales swhere dd. deptno = s. deptno (+) order by dd. rn; deptno deptname SALE_NUM PARENT_DEPTNO ---------------------------------------------------------------------------------

1 marketing department 0-1
2 Market 1 0 1
4 sales group 0 2
8 power 1 50000 4
10 Power Generation 3 60000 4
5 sales group 2 0 2
9. Power 2 10000 5
11 Power 4 10000 5
3 Market Division 2 0 1
6 sales group 3 0 3
12 petroleum 1 20000 6
14 petroleum 3 90000 6
7 Sales Group 4 0 3
13 petroleum 2 40000 7
15. Petroleum 4 110000 7

Count the turnover of all parent departments.

Thought 1:First, all the parent departments are checked out, and then a subquery is made to recursively query its subnodes, and then sum the subnodes. This idea is very simple, inefficient, and not recommended.

Idea 2:First, use a hierarchical query to find out the tree structure. To calculate the summary under a node, you need to use connect_by_root. connect_by_root under the same node is the same, and then group.

-- The implementation is as follows:

With temp as (select dd. deptno, nvl (s. sale_num, 0) sale_num, root_id, rnfrom (select d. deptno, connect_by_root (d. deptno) root_id, rownum rnfrom dept dstart with d. PARENT_DEPTNO in (select PARENT_DEPTNO from dept) connect by prior d. DEPTNO = d. PARENT_DEPTNO) dd, sales swhere dd. deptno = s. deptnoorder by dd. rn), temp1 as (select root_id, sum (sale_num) s_sum from temp group by root_id), temp3 as (-- add temp3 purely for reading convenience select d. deptno, cast (lpad ('', level * 2-1) | d. deptname as varchar2 (50) deptname, rownum rnfrom dept dstart 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.deptnoorder by rn; deptno deptname S_SUM

----------------------------------------------------------------------
1 marketing department 390000
2. Market 1, 130000
4 Sales Group 110000
8 power 1 50000
10 Power 3 60000
5. Sales Group 2 20000
9 power 2 10000
11 Power 4 10000
3 Market Department 2 260000
6 sales group 3 110000
12. Petroleum 1 20000
14. Petroleum 3 90000
7. Sales Group 4: 150000
13. Petroleum 2 40000
15. Petroleum 4 110000
15 rows have been selected.