Query related trees in Oracle

Format:
Select column
From table_name
Start with column = Value
Connect by prior parent primary key = Child foreign key

Select lpad ('', 4 * (level-1) | Name, job, ID, super from EMP
Start with super is null
Connect by prior id = super

 

 

Example:
Raw data: Select No, Q from a_example2

No name
----------------------------------------
001 A01
001 A02
001 A03
001 a04
001 a05
002 B01
003 c01
003 C02
004 d01
005 e01
005 e02
005 e03
005 e04
005 e05

The result is as follows:
001 A01; A02; A03
002 B01
003 c01; C02
004 d01
005 e01; e02; e03; e04; e05

Ideas:
1. There is a connect by clause after oracle8.1 to retrieve the entire tree data.
Create Table a_example1
(
NO char (3) not null,
Name varchar2 (10) Not null,
Parent char (3)
)

Insert into a_example1
Values ('001', 'old Wang ', null)

Insert into a_example1
Values ('20170901', 'lil', null)

Insert into a_example1
Values ('002', 'Kingdom 1', '001 ')

Insert into a_example1
Values ('20180101', 'Lee 1', '20180101 ')

Insert into a_example1
Values ('003 ', 'king 2', '001 ')

Insert into a_example1
Values ('20180101', 'dali 2', '20180101 ')

Insert into a_example1
Values ('003 ', 'John 1', '002 ')

Insert into a_example1
Values ('20170301', 'Lee 1', '20160301 ')

No name parent
001 Lao Wang
101 Lao Li
002 King 1 001
102 Dali 1 101
003 King 2 001
103 Dali 2 101
003 John 1 002
103 Xiao Li 1 102

// Retrieve data by family tree
Select * From a_example1

Select level, sys_connect_by_path (name, '/') path
From a_example1
Start with/* name = 'old Wang 'and */parent is null
Connect by parent = prior no
Result:
1/Lao Wang
2/Lao Wang/dawang 1
3/Lao Wang/dawang 1/Xiao wang 1
2/Lao Wang/dawang 2
1/Lao Li
2/Lao Li/Dali 1
3/Lao Li/Dali 1/Xiao Li 1
2/Lao Li/Dali 2

Based on the above ideas, we only need to make the original data into the following structure:
No name
001 A01
001 A01/A02
001 A01/A02/A03
001 A01/A02/A03/a04
001 A01/A02/A03/a04/a05
002 B01
003 c01
003 c01/C02
004 d01
005 e01
005 e01/e02
005 e01/e02/e03
005 e01/e02/e03/e04
005 e01/e02/e03/e04/e05

Group by no. The maximum value is the result.
No name
001 A01/A02/A03/a04/a05
002 B01
003 c01/C02
004 d01
005 e01/e02/e03/e04/e05

SQL statement:
Select No, max (sys_connect_by_path (name, ';') result from
(Select No, name, RN, lead (RN) over (partition by no order by RN) rn1
From (Select No, name, row_number () over (order by NO, name DESC) Rn from a_example2)
)
Start with rn1 is null connect by rn1 = prior Rn
Group by no

Statement Analysis:
1. Select No, name, row_number () over (order by NO, name DESC) Rn from a_example2
Sort by NO in ascending order, and sort by name in descending order to generate pseudo columns to form a Tree Structure
No name Rn
001 A03 1
001 A02 2
001 A01 3
002 B01 4
003 C02 5
003 c01 6
004 d01 7
005 e05 8
005 e04 9
005 e03 10
005 e02 11
005 e01 12

2. Select No, name, RN, lead (RN) over (partition by no order by RN) rn1
From (Select No, name, row_number () over (order by NO, name DESC) Rn from a_example2)
Generate a family spectrum, that is, the child node corresponds to the parent node, and the corresponding relationship is through Rn AND rn1. Lead is the RN value of the previous record.

No name rn rn1 001 A03 1 2 -- Note: For No = 001, the next record Rn = 2 001 A02 2 3 -- Note: For No = 001, next record Rn = 3 001 A01 3 -- Note: For No = 001, the next record RN is null
002 B01 4 003 C02 5 6 003 c01 6 004 d01 7 005 e05 8 9 005 e04 9 10 005 e03 10 11 005 e02 11 12 005 e01 12

3. Select No, sys_connect_by_path (name, ';') result from
(Select No, name, RN, lead (RN) over (partition by no order by RN) rn1
From (Select No, name, row_number () over (order by NO, name DESC) Rn from a_example2 ))
Start with rn1 is null connect by rn1 = prior Rn
Generate tree
No result
001; A01
001; A01; A02
001; A01; A02; A03
002; B01
005; e01
005; e01; e02
005; e01; e02; e03
005; e01; e02; e03; e04
005; e01; e02; e03; e04; e05
003; c01
003; c01; C02
004; d01

Group the above results by NO, and obtain the maximum value of the result. Therefore, change the preceding statement
Select No, max (sys_connect_by_path (name, ';') result from
(Select No, name, RN, lead (RN) over (partition by no order by RN) rn1
From (Select No, name, row_number () over (order by NO, name DESC) Rn from a_example2)
)
Start with rn1 is null connect by rn1 = prior Rn
Group by no
Obtain the expected result.