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

This article mainly introduces "the recursion principle in SQL". In the daily operation, I believe that many people have doubts about the recursion principle in SQL. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "Recursion principle in SQL". Next, please follow the editor to study!

Recursive query principle

Recursive queries in SQL Server are implemented through CTE (table expressions). Contains at least two queries, the first 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.

Termination condition of recursive 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.

Advantages of recursive query

High efficiency, under a large number of data sets, the speed is faster than the program query.

A common form of recursion, WITH CTE AS (SELECT column1,column2...) FROM tablename WHERE conditions UNION ALL SELECT column1,column2... FROM tablename INNER JOIN CTE ON conditions) Recursive query example

To create test data, there is an employee table Employee,ManagerID that is the parent of UserID, which is a very simple hierarchical model.

USE SQL_Road GO CREATE TABLE Employee (UserID INT, ManagerID INT, Name NVARCHAR (10)) INSERT INTO dbo.Employee SELECT 1dagger Nobles' UNION ALL SELECT 11pje 1, Noble A1' UNION ALL SELECT 12pr 1, Noble A2' UNION ALL SELECT 13 L1, Noble A3' UNION ALL SELECT 111pr 11, Noble B1' UNION ALL SELECT 112pr 11, Noble B2' UNION ALL SELECT 121pr 12lt C1'

Query the data in the Employee table

Query the direct superior Manager of each User

WITH CTE AS (SELECT UserID,ManagerID,Name,Name AS ManagerName FROM dbo.Employee WHERE ManagerID=-1 UNION ALL SELECT c. UserID c. ManagerID c. Name AS ManagerName FROM CTE P INNER JOIN dbo.Employee c ON p.UserID=c.ManagerID) SELECT UserID,ManagerID,Name,ManagerName FROM CTE

The results are as follows:

Let's interpret the code above.

1. Query ManagerID=-1, as the root node, which is the starting point of the recursive query.

2. The iterative formula is the query statement under UNION ALL. Call the CTE in the query statement, and the query statement is part of the CTE, that is, "call yourself", which is the true meaning of recursion.

The so-called iteration means that each recursion invokes the result set of the last query, and UNION ALL refers to putting the result set together each time.

3. The iterative formula executes a specific query using the result set returned by the previous query until CTE returns NULL or reaches the maximum number of iterations. The default value is 32. The final result set is the union of each result set returned by the iterative formula. The union is defined by the UNION ALL clause and can only be used with UNION ALL.

Query path

Let's query the PATH from the child node to the parent node through the hierarchy, and we modify the above code slightly:

WITH CTE AS (SELECT UserID,ManagerID,Name,CAST (Name AS NVARCHAR (MAX)) AS LPath FROM dbo.Employee WHERE ManagerID=-1 UNION ALL SELECT c. UserID ON p.UserID=c.ManagerID c. ManagerID journal c. Name journal p. LPathology stories->'+ c.Name AS LPath FROM CTE P INNER JOIN dbo.Employee c ON p.UserID=c.ManagerID) SELECT UserID,ManagerID,Name,LPath FROM CTE

CAST (Name AS NVARCHAR (MAX)) sets the length of the Name to the maximum to prevent the field from being too long to exceed the field length. The specific results are as follows:

At this point, the study of "the principle of Recursion in SQL" is over. I hope I can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.