Tree query in Oracle

Basic syntax

Select... from tabename start with cond1 connect by prior cond2 where cond2

Note: cond1 is the limit statement for the root node.

Cond2 is the connection condition, where prior indicates the previous record, indicating that the father of the record is the previous record

Cond3 is a filter condition.

Build an environment: you do not know

Create Table Family (
Id integer,
Parentid integer,
Name varchar2 (50)
)

Insert into family values (0, 0, 'A ')
Insert into family values (1, 0, 'B ')
Insert into family values (2, 1, 'C ')
Insert into family values (3, 1, 'D ')
Insert into family values (4,1, 'E ')
Insert into family values (5, 1, 'F ')

Example 1: traverse sub-nodes through the root node

-- Query the information of all the children whose father is equal to 1
Select * from Family Start With parentid = 1 connect by prior id = parentid

Example 2: Tracing to the root node through a subnode

Select * from Family Start with ID = 5 connect by prior parentid = ID

Note: If the ORA-01436 is reported: The coonect by loop in the user database, the parentid In the first data is changed to null, otherwise the loop find parentid will not find it!

Extension:PassLevelLevel of keyword Query

Select T. *, level from family t start with parentid = 1 connect by prior id = parentid -- the table must use an alias