How to realize Recursive query in SQL

How to implement recursive query in SQL? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

With cte as

(

Select Id,Pid,DeptName,0 as lvl from Department

Where Id = 2

Union all

Select d.Idred.Pidred.DeptNameauthorlvlplay1 from cte c inner join Department d

On c.Id = d.Pid

)

Select * from cte

Table structure:

Id Pid DeptName

-

1 0 headquarters

2 1 R & D Department

3 1 test department

4 1 quality Department

5 2 team 1

6 2 team 2

7 3 Test 1

8 3 Test 2

9 5 front end group

10 5 artists

Query results check all subordinate departments and corresponding levels of the department ID=2

Id Pid DeptName lvl

-

2 1 R & D Department 0

5 2 team 1 1

6 2 team 2 1

9 5 Front end Group 2

10 5 artists 2

Principle (excerpt from the Internet)

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.

This is the answer to the question about how to implement recursive query in SQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.