oracle tree query statement

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 name,job,id,super from emp
start with super is null
connect by prior id=super
Example:
Original 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 results to be achieved are as follows:
001 a01;a02;a03
002 b01
003 c01;c02
004 d01
005 e01;e02;e03;e04;e05
Ideas:
1. After ORACLE8.1, there is an connect by clause, and the whole tree data is taken out.
create table a_example1
(
no char(3) not null,
name varchar2(10) not null,
parent char(3)
)
insert into a_example1
values (' 001 ', 'Lao wang' null)
insert into a_example1
values (' 101 ', 'li', null)
insert into a_example1
values (' 002 ', '1' king of judah, '001')
insert into a_example1
values (' 102 ', 'big li 1', '101')
insert into a_example1
values (' 003 ', 'the king 2', '001')
insert into a_example1
values (' 103 ', 'big lee 2', '101')
insert into a_example1
values (' 003 ', 'wang 1', '002')
insert into a_example1
values (' 103 ', 'Ms. Li 1', '102')
NONAMEPARENT
001 king
101 li
002 King 1 001
102 Big Li 1 101
003 King 2 001
103 Dali 2 101
003 Xiao Wang 1 002
Xiao Li 1 102
// Fetch data according to family tree
select * from a_example1
select level,sys_connect_by_path(name,'/') path
from a_example1
start with /*name = 'Lao Wang' and*/ is null
connect by parent = prior no
Results:
1 / Lao wang
2 / Lao Wang/King 1
3 / Lao Wang/King 1/ Xiao Wang 1
2 / Lao Wang/King 2
1 / li
2 / Lao Li/Da Li 1
3 / Lao Li/Da Li/Xiao Li 1
2 / Lao Li/Da Li 2
According to the above idea, we just 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
Finally, it is grouped according to NO, and the maximum value is the desired result.
NO NAME
001 a01/a02/a03/a04/a05
002 b01
003 c01/c02
004 d01
005 e01/e02/e03/e04/e05
SQL statements:
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 ascending by NO and descending by NAME, generating pseudo columns for the purpose of forming a tree structure
NONAMERN
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)
The family tree is generated, that is, the child node has corresponding relation with its parent node, and the corresponding relation is through rn and rn1. Where lead is the RN value of the previous record
NONAMERNRN1 001 a03 1 2 --
For NO = 001, RN = 2 001 a02 2 3-- for NO = 001, a01 3-- for NO = 001, 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
Formal spanning tree
NORESULT
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 according to NO and take the maximum value of result, so change the above statement to
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
Get the desired result.