SQL Server common table expressions (CTE) Implement recursion, cte Recursion

SQL Server common table expressions (CTE) Implement recursion, cte Recursion

Introduction to common table expressions:

A common table expression (CTE) can be considered as a temporary result set defined within the execution range of a single SELECT, INSERT, UPDATE, DELETE, or create view statement. CTE is similar to a derived table. It is not stored as an object and is only valid during the query. Unlike a derived table, a common table expression (CTE) has an important advantage in that it can reference itself to create a recursive CTE. Recursive CTE is a public table expression that repeats the initial CTE to return a subset of data until the complete result set is obtained.

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', 'administrator A', '1' union select '3', 'administrator B ', '2' union select '4', 'member A', '2' union select '5', 'member AB ', '2' union select '6 ', 'Member Ba', '3' union select '7', 'member BB ', '3' union select '8', 'user aaa ', '4' union select '9', '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 child nodes of a specified node:

Use common SQL statements:

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 the specified node to find the insert into @ ResTab -- insert the initial data select Id for the table variable, @ level from Role_CTE where Id = @ nodewhile (@ ROWCOUNT> 0) begin set @ level = @ level + 1 insert into @ ResTab select B. id, @ level from @ ResTab a join Role_CTE B on. node = B. parentId and lv = @ level-1 -- join is equal to inner join (inner join) and Self-join endselect. node, B. name,. lv from @ ResTab a left join Role_CTE B on. node = B. id

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 yes, insert it and continue the loop.

PS: lv = @ level-1 is the key point. Otherwise, it will enter an endless loop. The function is to limit only one insert.

To limit the number of cycles, that is, the number of recursive layers, you only need to add a limit in 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, from which node is specified to find the set @ num = 1 -- specifies the recursive level, insert into @ ResTab: insert the initial data select Id for the table variable, @ level from Role_CTE where Id = @ nodewhile (@ ROWCOUNT> 0 and @ level <@ num) begin set @ level = @ level + 1 insert into @ ResTab select B. id, @ level from @ ResTab a join Role_CTE B on. node = B. parentId and lv = @ level-1 -- join is equal to inner join (inner join) and Self-join endselect. node, B. name,. lv from @ ResTab a left join Role_CTE B on. node = B. id

Of course, if the number of cycles is specified, you can determine the @ rowcount> 0 of the statement without using the while clause.

Use SQL CTE to implement:

Declare @ node int set @ node = 3; with temp_cteas (select Id, Name, 0 lv -- query the "Root node ", that is, the specified starting node from Role_CTE where Id = @ node union all select B. id, B. name,. lv + 1 from temp_cte a join Role_CTE B on. id = B. parentId) select * from temp_cte

Use CTE to control the number of recursive layers, 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 the "Root node ", that is, the specified starting node from Role_CTE where Id = @ node union all select B. id, B. name,. lv + 1 from temp_cte a join Role_CTE B on. id = B. parentId and. lv <@ num -- controls the number of recursive layers) select * from temp_cte

Search for all ancestor nodes of a specified node:

Use common SQL statements:

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 = 8 -- indicates the initial node ID, that is, starting from the specified node to find set @ num = 2 -- specifies the recursive level, that is, the number of cycles while (@ level <= @ num and @ node is not null) -- if it is null, it indicates that the parent-level begin insert into @ ResTab select @ node is not found, @ level set @ level = @ level + 1 select @ node = ParentId from Role_CTE where Id = @ nodeendselect. node, B. name,. lv from @ ResTab a left join Role_CTE B on. node = B. id

Use SQL CTE to implement:

Declare @ node int declare @ num intset @ node = 8; set @ num = 2; with temp_cteas (select Id, Name, ParentId, 0 lv -- query the "Root node ", that is, the specified starting node from Role_CTE where Id = @ node union all select B. id, B. name, B. parentId,. lv + 1 from temp_cte a join Role_CTE B on. parentId = B. id and. lv <@ num -- controls the number of recursive layers) select * from temp_cte

The above is a small series of SQL Server public table expressions (CTE) to implement recursive methods, I hope to help you, if you have any questions, please leave a message, the editor will reply to you in a timely manner, and I would like to thank you for your support for the help House website!