SQL parent node to find all child nodes, SQL parent node

SQL parent node to find all child nodes, SQL parent node

How can we save data in a tree structure? Commonly Used in SQL is the parent node method. Create a table as follows:

CREATE TABLE category ( id LONG, parentId LONG, name String(20) )INSERT INTO category VALUES ( 1, NULL, 'Root' )INSERT INTO category VALUES ( 2, 1, 'Branch1' )INSERT INTO category VALUES ( 3, 1, 'Branch2' )INSERT INTO category VALUES ( 4, 3, 'SubBranch1' )INSERT INTO category VALUES ( 5, 2, 'SubBranch2' )

The parent id indicates the parent node, and the name indicates the node name.

Assume that you want to obtain all the subnodes under a certain node (obtain the descendant Descendants). How can this problem be solved? If you use a program (Java/PHP) for recursive calling, it will be accessed back and forth between the database and the local development language, which is less efficient. So we hope we can do this at the database level-What should we do?

Recursion

After query, the best method (I think) is the SQL recursive CTE method. The so-called CTE refers to the Common Table Expressison public Table expression. "CTE is a very elegant existence," commented a netizen. The biggest benefit of CTE is the improvement of code readability, which is one of the essential qualities of good code. Recursive CTE makes it easier and easier to implement complex queries in an elegant and concise manner ." -- In fact, I am not familiar with SQL. You can just Google it.

How to Use CTE? We use a small database named SQLite to support it! Although he is not very small, he can also support the with statement of the latest SQL99. The example is as follows.

WITH w1( id, parentId, name) AS (SELECT category.id, category.parentId,             category.nameFROM category WHERE id = 1UNION ALL SELECT category.id, category.parentId,             category.nameFROM category JOIN w1 ON category.parentId= w1.id) 

SELECT * FROM w1; WHERE id = 1 is the id of the parent node. You can change it to your variable. To put it simply, recursive CTE contains at least two queries (also called members ). The first query is a fixed-point member. A fixed-point member is only a query that returns a valid table and is used as the basis or positioning point for recursion. The second query is called a recursive member, and the recursive reference to the CTE name is triggered. Logically, the internal application of the CTE name can be understood as the result set of the previous query. Recursive queries do not have explicit recursive termination conditions. Recursive queries are stopped only when the second recursive query returns an empty result set or exceeds the maximum number of recursion times. MAXRECURION is used to limit the number of recursion times.

The method for searching all parent nodes is provided accordingly (retrieving the ancestor Ancestors is to reverse the id and parentId)

WITH w1( id, parentId, name, level) AS  (    SELECT          id,          parentId,          name,        0 AS level      FROM          category        WHERE          id = 6     UNION ALL        SELECT          category.id,          category.parentId,          category.name ,        level + 1      FROM          category JOIN w1 ON category.id= w1.parentId )  SELECT * FROM w1; 

Helpless MySQL

SQLite is OK, but what about MySQL?

On the other side, MySQL, which everyone loves to use, ignores the with statement. The blog on the official website clearly states that it is not supported at all and is very inconvenient. Why is it so simple that things cannot be used? -- MySQL does not seem to plan to add the with cte function in future new versions. So we came up with a lot of ideas. Isn't it actually a recursive Program-It shouldn't be difficult-to write a function or a stored procedure? That's right, indeed. -- writing recursion is not a problem. The problem is that using SQL to write is a problem. -- or that sentence, "the line is like a mountain." Although it is a bit exaggerated, however, I think there should be not many people who understand both databases and various database dialects (stored procedures ~, -- No more details. It is the code post to paste it ~

I will not post SQL here. You can refer to "query all Tree nodes in MySQL" here.

So far, our goal can be said to have been achieved, and it is not bad, because it is not limited to a number of layers (previously, CMS often said "unlimited" classification ). -- In fact, in general, there are many layers that exceed three layers, which is very complicated. Generally, users cannot use such layers without special requirements. Therefore, under the constraints of a given number of layers, you can write standard SQL statements to complete this task-although it seems a bit dead ~~

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4FROM category AS t1LEFT JOIN category AS t2 ON t2.parentId = t1.idLEFT JOIN category AS t3 ON t3.parentId = t2.idLEFT JOIN category AS t4 ON t4.parentId = t3.idWHERE t1.id= 1

The method for searching all parent nodes is provided accordingly (retrieving the ancestor Ancestors is to reverse the id and parentId)

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
Left join category AS t2 ON t2.id = t1.parentId
Left join category AS t3 ON t3.id = t2.parentId
Left join category AS t4 ON t4.id = t3.parentId

WHERE t1.id = 10 optimized version

However, the generated results are a little strange compared with the first example, and it is not easy to use for Java. -- look for other examples.

SELECT
P1.id,
P1.name,
P1.parentId as parentId,
P2.parentId as parent2_id,
P3.parentId as parent3_id,
P4.parentId as parent4_id,
P5.parentId as parent5_id,
P6.parentId as parent6_id
FROM category p1
Left join category p2 on p2.id = p1.parentId
Left join category p3 on p3.id = p2.parentId
Left join category p4 on p4.id = p3.parentId
Left join category p5 on p5.id = p4.parentId
Left join category p6 on p6.id = p5.parentId
WHERE 1 IN (p1.parentId,
P2.parentId,
P3.parentId,
P4.parentId,
P5.parentId,
P6.parentId)

Order by 1, 2, 3, 4, 5, 6, and 7; the result is like this.

The method for searching all parent nodes is provided accordingly (the method for retrieving the ancestor Ancestors is to reverse the id and parentId, and change the field name IN)

SELECT           p1.id,         p1.name,         p1.parentId as parentId,         p2.parentId as parent2_id,         p3.parentId as parent3_id  FROM  category p1   LEFT JOIN  category p2 on p2.parentId  = p1.id  LEFT JOIN  category p3 on p3.parentId  = p2.id  WHERE 9 IN  (p1.id,              p2.id,              p3.id)    ORDER BY 1, 2, 3; 

This is quite common ~ Whether you are using SQLite or MySQL.

Other queries:

Query the total number of direct subnodes:

SELECT c.*,    (SELECT COUNT(*) FROM category c2 WHERE c2.parentId = c.id)     AS direct_childrenFROM category c

• Using the with statement recursion, easy-to-understand example (English), my first successful example is copy from here, and also can check the layers level and reverse parent node: https://www.valentina-db.com/dokuwiki/doku.php? Id = valentina: articles: recursive_query

• Source of standard writing: http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query

• Good Summary: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

• SQlite with statement usage http://blog.csdn.net/aflyeaglenku/article/details/50978986

• The tree structure made of closures (the book says this method is the best, but it also feels very advanced, in English) http://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/

The preceding Implementation Method for Finding all the sub-nodes in the SQL parent node is all the content that I have shared with you. I hope to give you a reference and support for the customer's home.