Oracle Query tree structure of it Ninja Turtles

SELECT statements in Oracle can be used with the start with ... The Connect by prior clause implements a recursive query, and connect by is used in a structured query,

The basic syntax is:

-------------------------------------------------------------------------------------

SELECT * from table name start with query condition 1

Connect by Query Condition 2

where query condition 3;

-------------------------------------------------------------------------------------

In short, a tree structure is stored in a table, for example, there are two fields in a list: Id,parentid a tree structure can be formed by representing the parent of each record. All records of this tree can be obtained by querying the above syntax.

Query condition 1, is the root node of the limited statement, of course, can relax the qualification to obtain multiple root nodes, is actually more than a tree.
Query condition 2, which is a join condition, which uses PRIOR to represent the previous record, such as connect by PRIOR Id=praentid means 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, which is a filter condition, is used to filter all records returned.

case : For Oracle for Simple tree queries (recursive queries), the table structure is as follows:

DeptID Number Department ID

Paredeptid Number Parent Department ID (owning department ID)

Name CHAR (in + Byte) department names

Query 1: Through the child node to the root node to be traced.

SELECT * from persons.dept start with deptid=76 connect by prior Paredeptid=deptid

Query 2: Traverse the child nodes through the root node.

SELECT * from persons.dept start with Paredeptid=0 connect by prior Deptid=paredeptid

Query 3: The level keyword can be used to query the hierarchy.

Select A.*,level from Persons.dept a start with Paredeptid=0 connect by prior Deptid=paredeptid

Ps:

What is followed by start with is the seed of recursion, the place where recursion begins;

The order of the fields behind connect by Prior is fastidious;

If prior default: You can only query the starting line that matches the condition, do not make recursive query;

Case:

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 a tree structure can be formed by representing the parent of each record. All records of this tree can be obtained by querying the above syntax.

which

Condition 1 is the limit 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 a prior,

For example, CONNECT by PRIOR org_id = parent_id means the org_id of the previous record is 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: Determine whether the node has no access to the child node, if any, then to its leftmost non-visited sub-node, 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, 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 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, 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 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.

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.

Make a little progress every day

Oracle Query tree structure of it Ninja Turtles