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

This article introduces how to use common table expressions to achieve recursion in SQL Server, the content is very detailed, interested friends can refer to, I hope it can be helpful to you.

Introduction to common table expressions:

A common table expression (CTE) can be thought of as a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. CTE is similar to a derived table in that it is not stored as an object and is only valid during the query. Unlike derived tables, common table expressions (CTE) have the important advantage of being able to reference themselves to create a recursive CTE. A recursive CTE is a common table expression that repeatedly executes the initial CTE to return a subset of data until the complete result set is obtained.

Let's create a table and insert some data:

Create table Role_CTE (Id int not null, Name nvarchar (32) not null, ParentId int not null) insert into Role_CTE (Id,Name,ParentId) select'1 'super admin','0' union select'2''superadmin', 'administrator' 1 'union select' 3''administrator' administrator'2 'union select' 4 'member AA','2' union select' 5''member AB','2' union select' 6''member BA' '3' union select' 7Zongjie 'member BB','3' union select' 8ZHJ 'user AAA','4' union select' 9ZJZ 'user BBA','7'-- create a composite clustered index create clustered index Clu_Role_CTE_Indexon Role_CTE (Id,ParentId) with (pad_index=on, fillfactor=50, drop_existing=off, statistics_norecompute=on) select * from Role_CTE

Find all descendant nodes of the specified node:

Use a normal sql statement to implement:

Declare @ level intdeclare @ node intdeclare @ ResTab table (node int not null, lv int not null) set @ level=0-- indicates the initial level set @ node=3-- indicates the initial node ID, that is, from which node to find insert into @ ResTab-- inserts the initial data select Id,@level from Role_CTE where Id=@nodewhile (@ @ ROWCOUNT > 0) begin set @ level=@level+1 insert into @ ResTab select b.Id for the table variable. @ level from @ ResTab a join Role_CTE b on a.node=b.ParentId and lv=@level-1-- join equals inner join (internal connection) and self-connection endselect a.nodejournal b.nameme a.lv from @ ResTab a left join Role_CTE b on a.node=b.Id

The above is based on the specified node ID (3), find that the parent node ID (that is, the field ParentId) is equal to the specified node ID, if any, insert, and continue the loop.

PS:lv=@level-1 is the key point, otherwise it will enter an endless loop, and the function is to limit the insertion only once.

If you need to limit the number of loops, that is, the number of layers of recursion, you only need to add a limit to the while condition. As follows:

Declare @ level intdeclare @ node intdeclare @ num intdeclare @ ResTab table (node int not null, lv int not null) set @ level=0-- indicates the initial level set @ node=3-- indicates the initial node ID, that is, the specified node starts looking for set @ num=1-- specifies the recursive level, that is, the number of cycles insert into @ ResTab-- inserts the initial data select Id,@level from Role_CTE where Id=@nodewhile (@ @ ROWCOUNT > 0 and @ level0) for the table variable.

Use SQL CTE to implement:

Declare @ node int set @ node=3;with temp_cteas (select Id,Name,0 lv-- query out the "root node", that is, the specified start node from Role_CTE where Id=@node union all select b.ID _ mend b. Name _

Use CTE to control the number of layers of recursion, similar to the above. As follows:

Declare @ node int declare @ num intset @ node=3;set @ num=1;with temp_cteas (select Id,Name,0 lv-- query out the "root node", that is, the specified start node, from Role_CTE where Id=@node union all select b.ID, from temp_ctea join Role_CTE b. Name. Lvyst1 from temp_ctea join Role_CTE b node.

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.