web123456

mysql Hierarchical Queries_SQL Advanced Queries (Hierarchical Queries, Recursive)

SQL Advanced Queries

hierarchical query

Hierarchical structure can be understood as a tree-like data structure, consisting of nodes. For example, a common organizational structure consists of a general manager, multiple deputy general managers, and multiple department heads. Another example is in manufacturing a product will have multiple sub-parts. A simple example is shown in the following diagram

The car serves as the root node and contains below it two child nodes, engine and body, which in turn are composed of other leaf nodes. (Leaf nodes denote nodes with no children)

If we were to store this product information in a database, the following data table would be formed.

We use the parent_product_id column to indicate which product is the parent of the current product.

So how do you do hierarchical queries with SQL statements? This is where the CONNECT BY and START WITH syntaxes come in.

Let's write out the SQL and then explain what it means.

SELECT

level,

id,

parent_product_id,

name

FROM

product

START WITH id = 1

CONNECT BY prior id = parent_product_id

ORDER BY

level

The query results are as follows:

Explain: LEVEL column indicates that the current product belongs to the first level. START WITH indicates from which product to start the query, CONNECT BY PRIOR indicates that the relationship between the parent node and the child node, each product ID points to a parent product.

If we change the starting point of the START WITH query to id = 2 and re-run the above SQL statement we will get the following result:

Since the product with id=2 is a body, we will only be able to check the sub-products under the body.

Of course, we can beautify the query results to make it more hierarchical, we let the root node below the LEVEL in front of a few spaces can be. Modify the above SQL a bit. Add 2*(LEVEL-1) spaces in front of each LEVEL, so that the second level will add two spaces and the third level will add four spaces.

SELECT

level,

id,

parent_product_id,

LPAD(' ', 2 * (level - 1)) || name AS name

FROM

product

START WITH id = 1

CONNECT BY prior id = parent_product_id

The query results have been layered as shown below:

recursive query

In addition to using the method we described above, you can also use a recursive query to get the same results. Recursion uses the WITH statement. A normal WITH statement can be viewed as a subquery, and we can use the contents of this subquery directly outside of WITH.

When querying recursively, we are referencing this subquery inside the WITH statement. Still in the above example, we use the WITH statement for the query.

WITH

temp_product (product_level, id, parent_product_id,name) AS

(

SELECT

0 AS product_level,id,parent_product_id,name

FROM

product

WHERE

parent_product_id IS NULL

UNION ALL

SELECT

tp.product_level + 1,,

p.parent_product_id,

FROM

product p

JOIN temp_product tp

ON

p.parent_product_id=

)

SELECT

product_level,

id,

parent_product_id,

LPAD(' ', 2 * product_level)

|| name AS NAME

FROM

temp_product;

The first SELECT statement looks up the root node and sets it to level = 0. The second SELECT statement is associated with the WITH statement itself, and the level is recursive by adding 1 to each level.

The query results are as follows:

You can see that the first column is the product hierarchy of the display, which is consistent with the results from our query above.

Also when using WITH recursion you can use depth-first and breadth-first search, what does that mean? Breadth-first means returning the sibling rows before returning the child rows, as in the above figure, which returns the body and engine rows first, followed by the child rows below them. On the contrary, depth-first is to first return the child rows of a parent node before returning the other sibling row.

All we need to do is add the following statement above the SELECT statement to implement a depth-first search query.

search depth FIRST BY id

SET order_by_id

The result is as follows, seeing that the child rows under each parent node are returned first, and then the other parent node.

Similarly, breadth-first uses the following SQL statement

search breadth FIRST BY id

SET order_by_id