Oracle start with connect by prior usage
Syntax: SELECT * FROM table name where Condition 1 start with condition 2 connect by prior current table field = Cascading table field start with and connect by prior statement complete recursive recording, forming a tree structure, usually Can be used in tables that have hierarchies. Start with indicates the starting record connect by prior specifies the field relationship code when associated with the current record:
--Create a departmental table, which is a hierarchical table with child records associated with the ID of the parent record through parent_id CREATE TABLE DEPT (ID number (9) PRIMARY KEY,--Department ID NAME VARCHAR2 (100),
--Department name parent_id number (9)
--Parent Department ID, which is associated with the upper department through this field; Insert the following data into the table, in order to make the code simple, a department has only one subordinate department that starts querying recursive records from the root node select * from dept start with Id=1 Connect by prior ID = parent_id; Here is the result of the query, start with id=1 to start the query from the id=1 record, recursive to the direction of the leaf, the recursive condition is id=parent_id, the current record ID equals the child record parent_id
Start querying recursive records from leaf nodes select * from dept start with Id=5 Connect by prior parent_id = ID;
The following is the query result, the recursive condition according to the parent_id of the current record, etc. with the ID of the parent record
Filter for query Results select * FROM dept where name '% sales% ' start with id=1 connect by prior ID = parent_id; As you can see in the following query results, start with ... connect by prior queries out the tree structure and then the where condition takes effect to filter all query results
Prior the role of the prior keyword means that a recursive query is not performed, only the records that satisfy id=1 are queried, the following is the result of removing the first query from the PRIOR keyword SELECT * from dept start with Id=1 Connect by Prior ID = parent_id;
Oracle start with connect by prior usage
