Oracle in Start With...connect by prior clause usageconnect by is used in structured queries, and its basic syntax is:
1 Select from TableName 2 start with condition 1 3 Connect by Condition 2 4 where condition 3;
Example:
1 Select from Table 2 start with org_id = ' hbhqfwgwpy '3 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, then by means of representing the parent of each record, a tree structure can be formed, and the query of the above syntax can get all the records of the tree. Among them:
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 the
previous record is represented by PRIOR , such as
connect by
PRIOR org_id = parent_id;
The org_idof the record is the parent_id of this record, 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 the process of
traversing the tree in sequence .
1. Description of the tree structure
The tree structure data is stored in the table, the hierarchical relationship between the data is a parent-child relationship, described by the relationship between columns and columns in the table, such as Empno and Mgr,empno in the EMP table represent the employee's number, 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 with the following command format:
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 the parent node on one side and the child node on the other side, thus determining whether the find tree structure is in the order of 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 to identify which node is the root node of the lookup tree structure, and 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, rather than in the order of traversing the tree structure.
The use of the start with...connect by clause of the Oracle Learning
