How to implement Tree structure Recursive query in sqlserver

This article shows you how to achieve tree structure recursive query in sqlserver, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

The common table expression (CTE), a temporary named result set defined in the query, will be used in the from clause. Each CTE is defined only once (but can be referenced any number of times within its scope) and will survive during the lifetime of the query. You can use CTE to perform recursive operations. The syntax to create is:

With () as () select * from

1. Generate data

-- menu directory structure table create table tb_menu (id int not null,-- primary key id title varchar (50),-- title parent int-- parent id);-- parent menu insert into tb_menu (id, title, parent) values (1, 'parent menu 1); insert into tb_menu (id, title, parent) values (2,' parent menu 2) Insert into tb_menu (id, title, parent) values; insert into tb_menu (id, title, parent) values; insert into tb_menu (id, title, parent) values;-- first-level menu insert into tb_menu (id, title, parent) values (6, 'first-level menu 6) Insert into tb_menu (id, title, parent) values (7, 'first-level menus 7); insert into tb_menu (id, title, parent) values (8,' level-1 menus 8); insert into tb_menu (id, title, parent) values (9, 'level-1 menus 9); insert into tb_menu (id, title, parent) values (10,' level-1 menus 10) Insert into tb_menu (id, title, parent) values (11, 'first-level menus 11); insert into tb_menu (id, title, parent) values (12,' level-1 menus 12); insert into tb_menu (id, title, parent) values (13, 'level-1 menus 13); insert into tb_menu (id, title, parent) values (14,' level-1 menus 14) Insert into tb_menu (id, title, parent) values (15, 'level 1 menus 15 minutes 4); insert into tb_menu (id, title, parent) values (16,' level 1 menus 16 minutes 4); insert into tb_menu (id, title, parent) values (17, 'level 1 menus 17 minutes 4); insert into tb_menu (id, title, parent) values (18,' level 1 menus 18 levels 5) Insert into tb_menu (id, title, parent) values (19, 'first-level menus 19); insert into tb_menu (id, title, parent) values (20,' first-level menus 20);-- second-level menus insert into tb_menu (id, title, parent) values (21, 'second-level menus 21); insert into tb_menu (id, title, parent) values (22,' second-level menus 22) Insert into tb_menu (id, title, parent) values (23, 'second-level menus 23); insert into tb_menu (id, title, parent) values (24,' second-level menus 24); insert into tb_menu (id, title, parent) values (25, 'second-level menus 25); insert into tb_menu (id, title, parent) values (26,' second-level menus 26) Insert into tb_menu (id, title, parent) values (27, 'second-level menus 27 levels 10); insert into tb_menu (id, title, parent) values (28,' second-level menus 28 levels 11); insert into tb_menu (id, title, parent) values (29, 'second-level menus 29 levels 12); insert into tb_menu (id, title, parent) values (30,' second-level menus 30 levels 13) Insert into tb_menu (id, title, parent) values (31, 'second-level menus 31); insert into tb_menu (id, title, parent) values (32,' second-level menus 32); insert into tb_menu (id, title, parent) values (33, 'second-level menus 33); insert into tb_menu (id, title, parent) values (34, 34) Insert into tb_menu (id, title, parent) values (35, 'second-level menus 35); insert into tb_menu (id, title, parent) values (36,' second-level menus 36); insert into tb_menu (id, title, parent) values (37, 'second-level menus 37);-- third-level menus insert into tb_menu (id, title, parent) values (38,' three-level menus 38). Insert into tb_menu (id, title, parent) values (39, 'three-level menus 39); insert into tb_menu (id, title, parent) values (40,' three-level menus 40); insert into tb_menu (id, title, parent) values (41, 'three-level menus 41); insert into tb_menu (id, title, parent) values (42,' three-level menus 42) Insert into tb_menu (id, title, parent) values (43, 'three-level menus 43); insert into tb_menu (id, title, parent) values (44,' three-level menus 44); insert into tb_menu (id, title, parent) values (45, 'three-level menus 45); insert into tb_menu (id, title, parent) values (46,' three-level menus 46) Insert into tb_menu (id, title, parent) values (47, 'three-level menus 47); insert into tb_menu (id, title, parent) values (48,' three-level menus 48); insert into tb_menu (id, title, parent) values (49, 'three-level menus 49); insert into tb_menu (id, title, parent) values (50,' three-level menus 50 minutes 31); commit

two。 Find all parent nodes

-- query all root nodes of a node (44) in a tree structure with cte_parent (id,title,parent) as (--start condition select id,title,parent from tb_menu where id = 44-- list child node query condition union all-- Recursive condition select a.idmage a.titlematia.parent from tb_menu an inner join cte_parent b-- perform recursion It's time to understand on a.id=b.parent) select * from cte_parent

3. Find subordinate nodes with level

-- query all child nodes (with cte_child (id,title,parent,level) as) under a node in the tree structure (--start condition select id,title,parent,0 as level from tb_menu where id = 6-color-list parent node query condition union all-- Recursive condition select a.id.title.parent) B.level+1 from tb_menu an inner join cte_child b on (a.parent=b.id)) select * from cte_child The above is how to implement tree structure recursive query in sqlserver. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.