IT Ninja Turtles Oracle query tree structure, Ninja Turtles oracle

IT Ninja Turtles Oracle query tree structure, Ninja Turtles oracle

The select statement in Oracle can use the start with... connect by prior clause to implement recursive queries. connect by is used in structured queries,

The basic syntax is:

Bytes -------------------------------------------------------------------------------------

Select * from table name start with query condition 1

Connect by query condition 2

Where query Condition 3;

Bytes -------------------------------------------------------------------------------------

Simply put, a tree structure is stored in a table. For example, if a table has two fields: id and parentid, a tree structure can be formed by indicating who the parent of each record is. You can use the preceding syntax to query all records of the tree.

Query condition 1 is a restricted statement of the root node. Of course, you can relax the conditions to obtain multiple root nodes, which are actually multiple trees.
Query condition 2 is a 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.
Query Condition 3 is a filtering condition used to filter all returned records.

Case: Perform a simple tree query (recursive query) on oracle. The table structure is as follows:

Deptid number department id

Paredeptid number parent department id (Department id)

Name char (40 Byte) department NAME

Query 1: Tracing data to the root node through a subnode.

Select * from persons. dept start with deptid = 76 connect by prior paredeptid = deptid

Query 2: traverse subnodes through the root node.

Select * from persons. dept start with paredeptid = 0 connect by prior deptid = paredeptid

Query 3: You can use the level keyword to query the level.

Select a. *, level from persons. dept a start with paredeptid = 0 connect by prior deptid = paredeptid

PS:

Start with is followed by the seeds of recursion, that is, the place where recursion begins;

The field sequence after connect by prior is exquisite;

If prior is used by default, only the starting row that meets the condition can be queried, and no recursive query is performed;

Case:

Select * from table

Start with org_id = 'hbh1_wgwpy'

Connect by prior org_id = parent_id;

Simply put, a tree structure is stored in a table. For example, if a table contains two fields: org_id and parent_id, a tree structure can be formed by indicating who the parent of each record is. You can use the preceding syntax to query all records of the tree.

Where:

Condition 1 is a restriction statement for the root node. Of course, you can relax the restriction to obtain multiple root nodes. Actually, it is multiple trees.

Condition 2 is the connection condition, where PRIOR represents the previous record,

For example, connect by prior org_id = parent_id indicates that the org_id of the previous record is the parent_id of this record, that is, the father of this record is the previous record.

Condition 3 is a filtering condition used to filter all returned records.

A brief introduction is as follows:

When scanning a tree structure table, you need to access each node in the tree structure. A node can only be accessed once. The steps are as follows:

Step 1: start with the root node;

Step 2: Access the node;

Step 3: Check whether the node has any child nodes that are not accessed. If yes, switch to the left-side unaccessed child node of the node and perform Step 2. Otherwise, perform step 4;

Step 4: If the node is the root node, the access is completed; otherwise, step 5 is executed;

Step 5: return to the parent node of the node and perform step 3.

In short, the process of scanning the entire tree structure is also the process of traversing the tree in the middle order.

1. Tree Structure Description

Data in the tree structure is stored in the table. The hierarchy between the data is the parent-child relationship. It is described by the relationship between columns in the table, such as EMPNO and MGR In the EMP table. EMPNO indicates the employee's number, MGR indicates the number of the person who leads the employee, that is, the MGR value of the child node is equal to the EMPNO value of the parent node. In each row of the table, there is a MGR representing the parent node (except the root node). The entire tree structure can be determined through the parent node of each node.

You can use the connect by and start with clauses in the SELECT command to query the tree structure relationships in the table. The command format is as follows:

SELECT...

Connect by {PRIOR column name 1 = column name 2 | column name 1 = PRIOR split name 2}

[Start with];

The connect by clause indicates that each row of data is retrieved in a hierarchical order and that the data in the table is connected to the tree structure. The PRIORY operator must be placed before one of the two columns of the connection relationship. For the parent-child relationship between nodes, the PRIOR operator represents the parent node on one side and the child node on the other side to determine whether the order of the tree structure is top-down or bottom-up. In addition to column names, you can also use list dashboard in the connection relationship. The start with clause is optional to identify the node used as the root node for searching the tree structure. If this clause is omitted, all rows that meet the query conditions are used as the root node.

Start with: You can specify not only one root node, but also multiple root nodes.

2. About PRIOR

The PRIOR operator is placed before and after the equal sign, which determines the query order.

When PRIOR is placed before a moderate number in the connect by clause, it is forced to search from the root node to the leaf node in sequence, that is, the parent node is directed to the child node through the tree structure, we call it a top-down approach. For example:

Connect by prior empno = MGR

When the PIROR operator is placed behind a moderate number in the connect by clause, it forces the sequential retrieval from the leaf node to the root node, that is, the child node is directed to the parent node through the tree structure, we call it a bottom-up approach. For example:

Connect by empno = PRIOR MGR

In this way, you should also specify a Start node.

3. Define the Start Node for search

When querying the tree structure from top to bottom, you can not only start from the root node, but also define any node as the Start Node to start searching down. In this way, the search result is a branch of the structure tree starting with this node.

4. Use LEVEL

In a table with a tree structure, each row of data is a node in the tree structure. Because the nodes are in different levels, each row of records can have a layer number. The layer number is determined based on the distance between the node and the root node. No matter which node starts, the layer number of the starting root node is always 1, and the child node of the root node is 2.

5. Crop nodes and branches

When querying the tree structure, you can remove some rows in the table, or cut off a branch in the tree, and use the WHERE clause to limit a single node in the tree structure to remove a single node in the tree, however, it does not affect its child nodes (When retrieving from top to bottom) or its predecessors (When retrieving from bottom to top ).

6. Sort display

Like other queries, the order by clause can also be used in tree structure queries to change the display ORDER of query results without having to traverse the tree structure.

Make a little progress every day
How to Write a query for the oracle tree structure?

Shi WeiJin
 
Oracle Database, Tree Structure Maintenance, adding Field Values

Why is this design? I think it is not very well designed. The essence of the problem is that the father of Labor Street is Nangang district, the father of Nangang district is Harbin, and the father of Harbin is Heilongjiang. The essence of this problem is to find the top node.
It is recommended that you first learn about the ConnectBy query exclusive to Oralce. In particular, you need to understand the virtual fields of level.
Modify the table structure to: the region code parent region code region name is enough
Then, use the connect by region code = parent code to find the region name of the root record and solve the problem.
Now, if we don't look for Heilongjiang, we need to find Harbin. Now we have to vomit blood.
=== Append the table. If the table already exists, you can use the view to change it to the parent and sub-structure. Using the character function to extract the zip code of the parent region can also solve the problem. In this way, the performance is poor, and a large number of full table scans will be performed. The foundation of performance is design.