Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to use parent nodes to find all child nodes in SQL. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Create the 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,' Branch2') INSERT INTO category VALUES (3,1, 'Branch3') INSERT INTO category VALUES (4,3,' SubBranch2') INSERT INTO category VALUES (5,2, 'SubBranch3')

Where parent id represents the parent node and name is the node name.

Suppose you currently want to get all the child nodes under a node (get offspring Descendants), what should you do? If you use a program (Java/PHP) to call recursively, you can imagine the inefficiency of going back and forth between the database and the local development language. So we want to be able to do it at the database level-- what should we do?

Recursive method

After inquiry, the best method (in my opinion) is the SQL recursive CTE method. The so-called CTE is the meaning of Common Table Expressison common table expression. Netizens commented: "CTE is a very elegant existence. The biggest benefit of CTE is the improvement of code readability, which is one of the necessary qualities of good code. Using recursive CTE can make complex queries more elegant and concise." As a matter of fact, I'm not familiar with SQL, so you can Google it.

How do I use CTE? We use the compact database SQLite, it supports! Despite his small size, he can also support the latest SQL99 with statement, as shown below.

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 WHERE id = 1 is the id of that parent node, and you can change it to your variable. To put it simply, a recursive CTE contains at least two queries (also known as members). The first query is a fixed-point member, which is just a query that returns a valid table and is used as the basis or anchor point for recursion. The second query is called a recursive member, and what makes the query a recursive member is that a 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. There is no explicit recursive termination condition for a recursive query, and recursion is stopped only when the second recursive query returns an empty set of results or exceeds the maximum number of recursions. The way to limit the number of recursions is to use MAXRECURION.

The method of finding all the parent nodes is given accordingly (to get the ancestor Ancestors, that is, to reverse 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 ok, and MySQL?

On the other side, MySQL, which everyone likes to use, ignores the with sentence. The blog on the official website makes it clear that it is not supported at all, and it is very inconvenient. Why can't you use it when it can be very simple? -- and MySQL doesn't seem to have any plans to add with's cte functionality in future releases. So people came up with a lot of ways. Isn't it just a recursive program-- it shouldn't be difficult-- to write a function or stored procedure? Yes, that's true.-- it's not a problem to write recursion, but it's a problem to write in SQL-- or the phrase, "interlaced like a mountain", although it's a bit of an exaggeration, I don't think there are many people who understand both database and various database dialects (stored procedures). Anyway, code posts are posted back and forth.

I'm not going to post SQL here. You can see here, "query all the child nodes in the tree in MySQL."

At this point, our goal can be said to have been achieved, and it is not bad, because there is no limit to the number of layers (the "infinite level" classification that CMS used to say before). In fact, in general, there are many layers with more than three layers, which is very complicated, and ordinary users can't use so many layers if they don't have special needs. So, under the constraint of a given number of layers, you can write a standard SQL to accomplish this task, even though it feels 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 of finding all the parent nodes is given accordingly (to get the ancestor Ancestors, that is, to reverse 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

But the result is a little strange compared to the first example, and it's not easy for Java to use-- then find another example.

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_idFROMcategory p1LEFT 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.parentIdWHERE 1 IN (p1.parentId, p2.parentId, p3.parentId P4.parentId, p5.parentId, p6.parentId)

ORDER BY 1, 2, 3, 4, 5, 6, 7; this finally looks like it, and the result is this.

The method of finding all parent nodes is given accordingly (to get the ancestor Ancestors, that is, to reverse id and parentId, and to change the field names in 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 very versatile ~ whether you SQLite or MySQL.

Other enquiries:

Query the total number of direct child nodes:

SELECT c.parents, (SELECT COUNT (*) FROM category c2 WHERE c2.parentId = c.id) AS direct_childrenFROM category c above is how to use parent nodes to find all child nodes in the SQL shared by Xiaobian. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.