SQL parent Node Lookup implementation of all child nodes _mssql

How do you save a tree-like structure of data? The parent-node method is commonly used in SQL. Create a table as follows

CREATE TABLE category (id long, parentid long, name String ())

INSERT into category VALUES (1, NULL, ' Root ')
INSERT into category values (2, 1, ' Branch1 ') inserts into
category values (3, 1, ' BRANCH2 ')
inserts into Catego Ry VALUES (4, 3, ' SubBranch1 ')
INSERT into category values (5, 2, ' SUBBRANCH2 ')

Where the parent ID represents the parents node and name is the node name.

Suppose you currently want to get all the child nodes under a node (get descendant descendants), what do you do? If you use the program (java/php) to call recursively, you will have a low efficiency between the database and the local development language. So we want to do it at the database level--How do we do that?

Recursive method

Upon enquiry, the best method (personally felt) is the method of SQL recursive CTE. A CTE is the meaning of a Common table Expressison common-form expression. "The CTE is a very elegant existence," said the Netizen. The biggest benefit of a CTE is the increased readability of the code, which is one of the qualities that good code has to offer. Using a recursive CTE makes it easier and more enjoyable to implement complex queries in elegant and concise ways. "--I'm not quite familiar with SQL, you can google the meaning of it."

How do you use a CTE? We use the Compact database SQLite, it is supported! Although he is small in size, he can support the latest SQL99 with statement, as the following example.

With W1 (IDs, ParentID, name) as 
(		SELECT 
			category.id, 
			category.parentid, 
            category.name
		from 
			category 
		WHERE 
			id = 1
	UNION all 
		SELECT 
			category.id, 
			category.parentid, 
            category.name
		From 
			category JOIN W1 on category.parentid= w1.id
) 

SELECT * from W1 where the WHERE id = 1 is the ID of the parent node and you can change to your variable. Simply put, a recursive CTE contains at least two queries (also known as members). The first query is a fixed-point member, and the fixed-point member is simply a query that returns a valid table for a recursive basis or anchor point. The second query, called a recursive member, makes the query known as a recursive member as a recursive reference to a CTE name that is triggered. The internal application of the CTE name can logically be interpreted as the result set of the previous query. Recursive queries do not have an explicit recursive termination condition, which stops recursion only if the second recursive query returns an empty result set or exceeds the maximum number of recursive times. The maximum number of recursive methods is to use Maxrecurion.

The corresponding method to find all parent nodes (get ancestor ancestors, is to turn IDs and ParentID in reverse)

With W1 (IDs, 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
      -  
        category JOIN W1 on category.id= w1.parentid
 )  

The Helpless MySQL

SQLite OK, and MySQL?

On the other side of the car, we all love to use the MySQL but disregard with the statement, the official website blog clearly that is not supported, very inconvenient, clearly can be very simple things why not use it? -and MySQL does not seem to plan to add the with CTE functionality to future releases. So we came up with a lot of ideas. Isn't it just a recursive program--it should be easy--write a function or a stored procedure, all right? Yes, that's true. Writing recursion is not a problem, the problem is to use SQL to write is a problem-or that sentence, "interlacing as Foster", although a bit exaggerated, but I would like to understand the database and understand the various database dialect writing (stored procedures) should not be a lot of people do not ~,--, Anyway is the code post to paste it ~

I do not have to paste the SQL here, you can see here, "MySQL in the tree-like all child nodes query"

At this point, our goal can be said to have been achieved, but also good, because this is not limited to the number of layers (formerly CMS often said "infinite class" classification). In fact, in general, the number of layers over three layers is many, very complex, the general user if no special needs, but also not so many layers. So, under the constraints of a given number of layers, you can write standard SQL to do the task-though it's a bit of a death story.

SELECT T1.name as Lev1, t2.name as Lev2, t3.name as Lev3, t4.name as lev4 from
category as T1 left
JOIN category A S t2 on t2.parentid = t1.id left
join category as T3 on t3.parentid = t2.id left
join category as T4 on T4.parenti D = t3.id
WHERE t1.id= 1

The corresponding method to find all parent nodes (get ancestor ancestors, is to turn IDs and ParentID in reverse)

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 resulting result is a bit odd compared to the first example, and it's not good 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, 7; This is finally like a point, the result is such a son.

The corresponding method is given to find all the parent nodes (get the ancestor ancestors, which is to turn the ID and the ParentID, and the field names to be changed 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 to P2.parentid  = P1.id left
  join  Category P3 on P3.parentid  = p2.id
  WHERE 9  in (P1.id,  
            p2.id,  
            p3.id)  
  

This is very common ~ whether you SQLite or MySQL.

Other queries:

Total number of direct child nodes queried:

Select c.*    (select COUNT (*) from category C2 WHERE C2.parentid = c.id) as 
    Direct_children from
category C

• Using the With statement recursion, an easy to understand example (English), my first successful example is copy from here, in addition to the level of the layer and the reverse of the parent node: https://www.valentina-db.com/dokuwiki/ Doku.php?id=valentina:articles:recursive_query

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

• Good summary post (in English): http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

SQLite with statement use Chinese translation (too obscure, not understand birds) http://blog.csdn.net/aflyeaglenku/article/details/50978986

• A tree structure made with closures (the book says this method is the best, but at the same time feel very advanced, English) http://charlesleifer.com/blog/ querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/

The above SQL parent node to find all the child nodes of the implementation method is small series to share all the content, hope to give you a reference, but also hope that we support the cloud-dwelling community.