Source: http://blog.csdn.net/itmyhome1990/article/details/16338637 Oracle is a relational database management system that organizes data in tables, and the data in some tables shows a tree-structured connection. For example, there are the following cases:
The data is an excerpt and the field value means
Tax authority code, tax authority name, higher tax Authority code, tax authority levelSELECT * from EXTERN_DM_SWJG query when the default order is the above order, you can see that there is confusion and no special structural characteristics.
And the desired results are as follows:
SJ_SWJG_DM is the first of the null and the root node, careful observation is the tree structure query results, may not be very intuitive, see clearly
1. Description of the tree structureTree structure data is stored in the table, the hierarchical relationship between the data is a parent-child relationship, through the table column and the relationship between columns, through the parent node of each node, you can determine the entire tree structure. 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 column name 2} [START with] ... which
the CONNECT by clause indicates that each row of data will be retrieved in a hierarchical order, and specifies that the data in the table be connected to the tree structure's relationship. The priory 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.
This determines whether the order is top-down or bottom-up in the search tree structure。 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.
Example 1 displays the table's data in a tree-structured manner.
[SQL]View Plaincopy
- Select Swjg_dm,swjg_mc,sj_swjg_dm,swjg_level
- From EXTERN_DM_SWJG
- Connect by prior SWJG_DM = Sj_swjg_dm
- Start with sj_swjg_dm is null
2. About Prior
The prior operator is placed before and after the equals sign, determining the order in which the query is retrieved.
Example 2 bottom-up query starting from a node
[SQL]View Plaincopy
- Select Swjg_dm,swjg_mc,sj_swjg_dm,swjg_level
- From EXTERN_DM_SWJG
- Connect by SWJG_DM = prior sj_swjg_dm
- Start with swjg_dm = ' 16107100004 '
3. Use levelIn 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.
In a query, you can use the pseudo-column level to display the hierarchy of data for each row. Level returns the hierarchy of the current node in the tree structure, which we can use to control the depth of the traversal of the tree structure. Example 3 shows the row data and the layer number in the table.
[SQL]View Plaincopy
- Select Level , Swjg_dm,swjg_mc,sj_swjg_dm,swjg_level
- From EXTERN_DM_SWJG
- Connect by prior SWJG_DM = Sj_swjg_dm
- Start with sj_swjg_dm is null
Pseudo-column level is numeric and can be used for various calculations in the SELECT command.
Example 4 uses the level to change the display of the query results.
[SQL]View Plaincopy
- Select Lpad (Level,level*3, ") as" level ", Swjg_dm,swjg_mc,sj_swjg_dm,swjg_level
- From EXTERN_DM_SWJG
- Connect by prior SWJG_DM = Sj_swjg_dm
- Start with sj_swjg_dm is null
In select using the function Lpad, refer to the Lpad function of oracal for the use of the Lpad function
4. 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).
Example 5 cut only a single node in the tree 16107100003 Fufeng County State Tax Bureau
[SQL]View Plaincopy
- Select Lpad (Level,level*3, ") as" level ", Swjg_dm,swjg_mc,sj_swjg_dm,swjg_level
- From EXTERN_DM_SWJG
- where SWJG_DM! =' 16107100003 '
- Connect by prior SWJG_DM = Sj_swjg_dm
- Start with sj_swjg_dm is null
In this query, only the single node in the tree is swjg_dm to 16107100003, but its child nodes still exist. If you want to cut a branch in the tree structure, use the Connect by clause. The CONNECT by clause qualifies the entire branch in the tree structure to prune a single node on the branch and to prune its descendant nodes (when it is retrieved from the top down) or the predecessor node (when retrieving from the bottom).
Example 6 Removing a branch of node 16107100003
[SQL]View Plaincopy
- Select Lpad (Level,level*3, ") as" level ", Swjg_dm,swjg_mc,sj_swjg_dm,swjg_level
- From EXTERN_DM_SWJG
- Connect by prior SWJG_DM = Sj_swjg_dm
- and swjg_dm! =' 16107100003 '
- Start with sj_swjg_dm is null
This query result is different from Example 5, in addition to cut off a single node 16107100003, also 16107100003 of the child node 16107100004 is cut off, that is, 16107100003 of this branch was cut off.
Of course, the WHERE clause can be used in conjunction with the Connect by clause to cut off a single node and a branch in the tree at the same time.
When you use the SELECT statement to report a tree structure report, you should be aware that the Connect by clause cannot act on the table join that appears in the WHERE clause. If you need to make a connection, you can create a view with the tree structure, and then connect the view to the other tables to complete the query you want.
Start with and connect by in Oracle layered queries (tree structure query)
