Oracle tree summary -- connect_by_root, oracleconnectby

Oracle tree summary -- connect_by_root, oracleconnectby

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 1', 1 );
Insert into dept values (3, 'market Division II ', 1 );
Insert into dept values (4, 'sale group', 2 );
Insert into dept values (5, 'sale group', 2 );
Insert into dept values (6, 'sale three group', 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 (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 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
(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 (-- added temp3 purely for reading convenience
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 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.