Connect by is used in structured queries, and its basic syntax is:
Select ... from tablename
start with condition 1
Connect by Condition 2
Where Condition 3;
Cases:
SELECT * FROM table
Start with org_id = ' hbhqfwgwpy '
Connect by prior org_id = parent_id;
In short, a tree structure is stored in a table, for example, there are two fields in a list:
org_id,parent_id so by indicating who the parent of each record is, a tree structure can be formed. All records of this tree can be obtained by querying the above syntax.
which
Condition 1is the limit statement of the root node, of course, you can relax the qualification to obtain multiple root nodes, is actually more than a tree.
Condition 2 is a join condition in which
PRIOR represents the previous record , such as
connect by
PRIOR org_id = parent_id; that is,
the org of the previous record. _ID is the parent_id of thisrecord, that is, the father of this record is the previous record.
Condition 3 is a filter condition that is used to filter all records returned.
The following is a brief introduction:
When you scan a tree structure table, you need to access each node of the tree structure, one node can access only once, and the steps to access it are as follows:
The first step: starting from the root node;
The second step: access the node;
The third step: to determine whether the node has no access to the child node, if any, then to its leftmost sub-section of the left, and perform the second step, otherwise perform the fourth step;
Fourth step: If the node is the root node, the access is complete, otherwise, the fifth step;
Fifth step: Return to the parent node of the node and perform the third step.
In summary: The process of scanning the entire tree structure is also
Middle Sequence Traversal treeThe process.
1. Description of the tree structure
The tree structure data is stored in the table, and the hierarchical relationship between the data is the parent-child relationship, described by the relationship between columns and columns in the table, such as empno and MGR in the EMP table. Empno represents the employee's number, and Mgr represents the number of the person who led the employee, that is, the MGR value of the child node equals the Empno value of the parent node. In each row of the table is a MGR (outside the root node) that represents the parent node, and the entire tree structure can be determined through the parent node of each node.
Use the connect by and start with clauses in the SELECT command to query a tree-structured relationship in a table. Its command format is as follows:
SELECT ...
CONNECT by {PRIOR column name 1 = column name 2| column name 1=prior split name 2}
[START with];
Where: The Connect by clause indicates that each row of data will be retrieved in a hierarchical order, and that the data in the table will be linked to the tree-structured relationship. The prior operator must precede one of the two columns of a connection relationship. For parent-child relationships between nodes,
The prior operator represents a parent node on one side and a child node on the other side, determining whether the find tree structure is top-down or bottom-up。 In connection relationships, column expressions are also allowed, in addition to the use of column names. The START with clause is optional and is used to identify which node is the root node of the lookup tree structure. If the clause is omitted, all rows that satisfy the query criteria are used as the root node.
START with: Not only can you specify a root node, but you can also specify multiple root nodes.
2. About Prior
The prior operator is placed before and after the equals sign, determining the order in which the query is retrieved.
When Prior is placed before the equals sign in the Connect by clause, it forces the sequential retrieval from the root node to the leaf node, that is, from the parent node to the child node in the direction of the tree structure, which we call the top-down approach. Such as:
CONNECT by PRIOR Empno=mgr
When the piror operator is placed after the equals sign in the Connect by clause, the sequential retrieval from the leaf node to the root node is enforced, that is, the child node is directed to the parent node through the tree structure, which we call the bottom-up approach. For example:
CONNECT by Empno=prior MGR
In this way, you should also specify a starting node.
3. Define a lookup Start node
When querying the tree structure from top to bottom, you can start looking down not only from the root node, but also from any node as the starting node. The result of this lookup is a branch of the tree that starts with that node.
4. Use level
In a table with a tree structure, each row of data is a node in the tree structure, and each row of records can have a layer number because the nodes are in different hierarchical locations. The layer number is determined by the distance between the node and the root node. The starting root node is always 1, the child node of the root node is 2, and so on, regardless of which node it starts from. Figure 1.2 shows the hierarchy of the tree structure.
5. Cropping of nodes and branches
When querying a tree structure, you can remove some rows from the table, or you can cut out a branch in the tree, and use the WHERE clause to qualify a single node in the tree structure to remove a single node in the tree, but it does not affect its descendant nodes (when it is retrieved from the top down) or the predecessor node (when retrieving from the bottom).
6. Sort Display
As in other queries, you can also use the ORDER BY clause in a tree-structured query to change the order in which the results of the query are displayed instead of traversing the tree structure. Posted on 2010-03-05 18:02xzc Reading (31541) Comments (2) edit Collection Category: Oracle
Comments:# re:oracle the start With...connect by clause 2010-03-05 18:04 | XZC
Start with ... The Connect by clause recursive query is typically used for a table to maintain the tree structure of the application.
To create a sample table:
CREATE TABLE Tbl_test
(
ID number,
NAME VARCHAR2 (+),
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 ', ' 121 ', ' 2 ');
Recursively from Root to tree
SELECT * FROM Tbl_test
Start with id=1
Connect by Prior ID = PID
Recursive from the distal to the tree root
SELECT * FROM Tbl_test
Start with id=5
Connect by prior PID = ID
=====
Simple tree Query for Oracle (recursive query)
DeptID Paredeptid NAME
Number number CHAR (+ Byte)
Department ID parent Department ID (owning Department ID) Department name
The child node is traced to the root node.
SQL code
SELECT * from persons.dept start with deptid=76 connect by prior Paredeptid=deptid
SQL code
SELECT * from persons.dept start with deptid=76 connect by prior Paredeptid=deptid
SELECT * from persons.dept start with deptid=76 connect by prior Paredeptid=deptid
Traverse the child nodes through the root node.
SQL code
SELECT * from persons.dept start with Paredeptid=0 connect by prior Deptid=paredeptid
SQL code
SELECT * from persons.dept start with Paredeptid=0 connect by prior Deptid=paredeptid
SELECT * from persons.dept start with Paredeptid=0 connect by prior Deptid=paredeptid
The level keyword allows you to query your hierarchy.
SQL code
Select A.*,level from Persons.dept a start with Paredeptid=0 connect by prior Deptid=paredeptid
SQL code
Select A.*,level from Persons.dept a start with Paredeptid=0 connect by prior Deptid=paredeptid
Select A.*,level from Persons.dept a start with Paredeptid=0 connect by prior Deptid=paredeptid
Review again: Start with ... the use of Connect by, the start with behind is the seed of recursion.
The seed of recursion is the place where the recursion begins. The "prior" in the back of connect by is the default: it can only query the starting line that matches the condition, and does not make a recursive query;
The fields that are placed behind connect by prior are related and indicate the direction of the query.
Exercise: Get the top node from a child node
SQL code
Select First_value (DeptID) over (ORDER by level DESC ROWS unbounded preceding) as Firstdeptid from persons.dept start with Deptid=76 Connect by Prior Paredeptid=deptid
Use of the start With...connect by clause in Oracle
