Start with… in Oracle... Understanding and usage of connect

Start with... connect by in Oracle implements multi-level Recursive understanding and usage of departments

 

The basic syntax is:

Select... from tablename start with cond1

Connect by cond2

Where cond3;

Simply put, a tree structure is stored in a table. For example, if a table contains two fields: ID and parentid, who is the parent of each record, to form a tree structure. You can use the preceding syntax to query all records of the tree.

Cond1 is a limit statement for the root node. Of course, you can relax the limit conditions to obtain multiple root nodes. Actually, it is multiple trees.

Cond2 is the connection condition, where prior represents the previous record. For example, connect by prior id = praentid indicates that the ID of the previous record is the praentid of this record, that is, the father of this record is the previous record.

Cond3 is a filter condition used to filter all returned records.

Example:

Create an example table:

Create Table tbl_test

(

ID number,

Name varchar2 (100 byte ),

PID Number default 0

);

Insert test data:

Insert into tbl_test (ID, name, pid) values ('1', '10', '0 ');

Insert into tbl_test (ID, name, pid) values ('2', '11', '1 ');

Insert into tbl_test (ID, name, pid) values ('3', '20', '0 ');

Insert into tbl_test (ID, name, pid) values ('4', '12', '1 ');

Insert into tbl_test (ID, name, pid) values ('5', '123', '2 ');

After data is inserted:

 

Understanding and usage of start with... connect by in Oracle

 

The basic syntax is: Select... from tablename start with cond1 Connect by cond2 Where cond3; Simply put, a tree structure is stored in a table. For example, if a table contains two fields: ID and parentid, who is the parent of each record, to form a tree structure. You can use the preceding syntax to query all records of the tree. Cond1 is a limit statement for the root node. Of course, you can relax the limit conditions to obtain multiple root nodes. Actually, it is multiple trees. Cond2 is the connection condition, where prior represents the previous record. For example, connect by prior id = praentid indicates that the ID of the previous record is the praentid of this record, that is, the father of this record is the previous record. Cond3 is a filter condition used to filter all returned records. Example: Create an example table: Create Table tbl_test ( ID number, Name varchar2 (100 byte ), PID Number default 0 ); Insert test data: Insert into tbl_test (ID, name, pid) values ('1', '10', '0 '); Insert into tbl_test (ID, name, pid) values ('2', '11', '1 '); Insert into tbl_test (ID, name, pid) values ('3', '20', '0 '); Insert into tbl_test (ID, name, pid) values ('4', '12', '1 '); Insert into tbl_test (ID, name, pid) values ('5', '123', '2 '); After data is inserted:   Recursion from root to tree end (retrieve all vertices) Select * From tbl_test Start with ID = 1 Connect by prior id = PID Display result: Recursion from the terminal to the root of the tree (only the root node is taken, and the root node of the root node is ......) Select * From tbl_test Start with ID = 5 Connect by prior pid = ID Result:     Note: If prior is written on another field, use start with to associate the value of this record with the value of another field in another record; Start with can be left blank. The default value is all records. For example Select * From tbl_test Start with ID = 5 Connect by prior pid = ID Because pid = 2 and ID = 5 are the same data in this data table, it can be understood: Select * From tbl_test Start with PID = 2 Connect by prior pid = ID (I think this is a better understanding)

**************************************** ************

This statement can be directly displayed in different layers.

Select lpad ('|-', (level-1) * 4, '|-') | lpad (', 2) | di_name | rpad (', 2)
Di_name, di_id
From t_dictionary_link connect by prior di_id = di_protypeid
Start with di_id =-1

 

|-"Company architecture" 2
|-"Core code technology" 3
|-"Development Department" 4
|-"Java group" 11
|-[. Net group] 102
|-"Sales Department" 261
|-"Customer service department 』