Oracle Tree Query Statement _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 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 results that need to be achieved are:
001 A01;A02;A03
002 B01
003 C01;C02
004 D01
005 e01;e02;e03;e04;e05
Ideas:
1, after ORACLE8.1 have a connect by clause, take out the whole tree data.
CREATE TABLE A_example1
(
No char (3) is not NULL,
Name VARCHAR2 (ten) is not NULL,
Parent char (3)
)
INSERT INTO A_example1
VALUES (' 001 ', ' Old King ', null)
INSERT INTO A_example1
VALUES (' 101 ', ' Lao Li ', null)
INSERT INTO A_example1
VALUES (' 002 ', ' King 1 ', ' 001 ')
INSERT INTO A_example1
VALUES (' 102 ', ' da li 1 ', ' 101 ')
INSERT INTO A_example1
VALUES (' 003 ', ' King 2 ', ' 001 ')
INSERT INTO A_example1
VALUES (' 103 ', ' da li 2 ', ' 101 ')
INSERT INTO A_example1
VALUES (' 003 ', ' Xiao Wang 1 ', ' 002 ')
INSERT INTO A_example1
VALUES (' 103 ', ' Xiao Li 1 ', ' 102 ')
No Name Parent
001 Old Wang
101 Old Lee
002 King 1 001
102 Big Lee 1 101
003 King 2 001
103 Big Lee 2 101
003 Xiao Wang 1 002
103 Xiao Li 1 102
To fetch data by family tree
SELECT * FROM A_example1
Select Level,sys_connect_by_path (Name, '/') path
From A_example1
Start with/*name = ' Lao Wang ' and*/parent is null
Connect by parent = Prior No
Results:
1/Lao Wang
2/Old King/King 1
3/Lao Wang/King 1/Xiao Wang 1
2/Old King/King 2
1/Lao Li
2/Lao li/da Li 1
3/Lao li/da li 1/Xiao Li 1
2/Lao li/da Li 2
In line with the above idea, we just have to make the original data structure as follows:
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, a maximum value is the desired result, grouped by No.
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 ()-no,name desc) rn from A_example2)
)
Start with RN1 are 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
Sorted by no ascending order, sorted by name in descending order, resulting in 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. by-RN) RN1
From (select No,name,row_number ()-no,name desc) rn from A_example2)
The generation of family spectrum, that is, the child node and the parent node has a corresponding relationship between the corresponding relationship through RN and RN1. Where the lead is the RN value of the previous record
No Name RN RN1 001 A03 1 2--
Note: For no=001, the next record of Rn=2 001 A02 2 3-note: For no=001, the next record of Rn=3 001 A01 3-Note: For NO=001, its next record of the RN is NULL
002 B01 4 003 C02 5 6 003 C01 6 004 D01 7 005 e05 8 9 005 e04 9 005 e03 005 E02 one 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 () no,name desc) rn from A_example2)
Start with RN1 are null connect by RN1 = Prior RN
Formal Spanning 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;d 01
The above results are grouped by no, the maximum result can be obtained, so the above statement is changed 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 ()-no,name desc) rn from A_example2)
)
Start with RN1 are null connect by RN1 = Prior RN
GROUP BY No
Get the results you want.