SQL recursive query, the execution steps of the fantasy CTE recursion

Today, a recursive query for SQL is used. A recursive query is a CTE statement with XX as (...). ) is implemented.

If the table category data is as follows.

We want to find the machine gun. This subcategory is extremely hierarchical (querying all levels of nodes through child nodes). The following is a query statement

With TT as (SELECT categoryid,name,parent,0 level from  dbo. Category WHERE categoryid=15  --anchor member Union allselect c.categoryid,c.name,c.parent,tt.level+1 level from Category C JOIN TT on TT. Parent=c.categoryid  --recursive member) SELECT * from TT

Execution results

CTE Recursion Description:

Above the script, I annotated the anchor member, and the recursive member.

The anchor member forms the base result set of the query. is actually the "intro" Data you use for the query. When the recursive CTE starts executing, the first execution, the locating member is executed, and the result set is sent.

A recursive member is a statement that executes each time recursively. Personal fantasy, each time recursion, the result set of the last recursive query is passed to the recursive statement, and continue to perform recursion

In recursion, if the last query did not return a result set, the recursion is terminated . (This makes me tangle for a long time, because there is no display of the famous when the end of recursion ~)

The execution Steps--------------------

To perform an anchored member first:

SELECT categoryid,name,parent,0 level from  dbo. Category WHERE categoryid=46

and get the result set:

Then perform the first recursion:

SELECT c.categoryid,c.name,c.parent,tt.level+1 level from Category C joins TT on TT. Parent=c.categoryid

When performing recursion, join the CTE statement itself, that is, the statement in Tt,join is the same as the statement in the with AS. Actually statements can be made into Jiangzi

SELECT c.categoryid,c.name,c.parent,tt.level+1 level from Category C joins (--select categoryid,name,parent,0  level FROM dbo. Category WHERE categoryid=15  Gorgeous ignored--union all  gorgeous ignore the select C.categoryid,c.name,c.parent,tt.level+1 level From Category C joins TT on TT. Parent=c.categoryid--We continue to recursion ~) TT on TT. Parent=c.categoryid

This recursion can be split into two operations, query results and continue recursion.

1. Because it is the first recursion, the result set that executes the positioned member is brought into the query. Actually, it's jiangzi.

Select C.categoryid,c.name,c.parent,tt.level+1 level from Category C joins (select categoryid,name,parent,0  level FROM dbo. Category WHERE categoryid=46) TT on TT. Parent=c.categoryid

Results were found:

Then continue to recursion:

Recursive time statement or Jiangzi:

SELECT c.categoryid,c.name,c.parent,tt.level+1 level from Category C joins (--select categoryid,name,parent,0  level FROM dbo. Category WHERE categoryid=15  Gorgeous ignored--union all  gorgeous ignore the select C.categoryid,c.name,c.parent,tt.level+1 level From Category C joins TT on TT. Parent=c.categoryid--We continue to recursion) TT on TT. Parent=c.categoryid

For this recursive query, the result set used is the last recursive query (no longer the result set of the anchor member). This is the result set.

The execution statement is Jiangzi

Select C.categoryid,c.name,c.parent,tt.level+1 level from Category C JOIN (select C.categoryid,c.name,c.parent, Tt.level+1 level from Category C joins (SELECT categoryid,name,parent,0 level from  dbo. Category WHERE categoryid=46) TT on TT. Parent=c.categoryid) TT on TT. Parent=c.categoryid

The data for the first-tier join is the statement of the last recursive query.

Results were found:

OK, continue with the recursion.

Continue with the previous steps and bring the result set of the last query into the query.

The execution statement is actually Jiangzi:

Select C.categoryid,c.name,c.parent,tt.level+1 level from Category C JOIN (select C.categoryid,c.name,c.parent, Tt.level+1 level from category C joins (select C.categoryid,c.name,c.parent,tt.level+1 level from category C join (select C ategoryid,name,parent,0 level from  dbo. Category WHERE categoryid=46) TT on TT. Parent=c.categoryid) TT on TT. Parent=c.categoryid) TT on TT. Parent=c.categoryid

The resulting set of results:

What also wood has, terminates recursion.

The result of each recursion is then set together with union all. The final query results are obtained.

This step is a fantasy, I also envision other ways. But there are problems in terminating recursion, so the article is eventually written as Jiangzi.

Reference article: https://www.cnblogs.com/youngmin/p/6256478.html

SQL recursive query, the execution steps of the fantasy CTE recursion