SQL Server uses the tree structure recursive query (infinitus classification) method. SQL server uses the Tree Structure

SQL Server uses the tree structure recursive query (infinitus classification) method. SQL server uses the Tree Structure

Starting from SQL Server 2005, we can use CTE to support recursive queries. CTE is the common table expression.

Baidu encyclopedia

A common table expression (CTE) is a temporary naming result set defined in the query and will be used in the from clause. Each CTE is defined only once (but can be referenced any time in its scope) and will survive during the query period. You can use CTE to perform recursive operations. The created syntax is:

with <name of you cte>(<column names>)as(<actual query>)select * from <name of your cte>

1. generate data

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

2. Search for all upper-level nodes

-- Query all the upper-level root nodes of a node (44) in the tree structure with cte_parent (id, title, parent) as (-- start condition select id, title, parent from tb_menu where id = 44 -- list subnode query conditions union all -- Recursive condition select. id,. title,. parent from tb_menu a inner join cte_parent B -- execute recursion. here we need to understand on. id = B. parent) select * from cte_parent;

3. Search for lower-level nodes

-- Query all subnodes under a node in the tree structure (with cte_child (id, title, parent, level) as (-- start condition select id, title, parent, 0 as level from tb_menu where id = 6 -- list the query conditions of the parent node, union all -- Recursive condition, select. id,. title,. parent, B. level + 1 from tb_menu a inner join cte_child B on (. parent = B. id) select * from cte_child;

The above SQL Server method for implementing tree-like recursive queries (infinitus classification) is all the content that I have shared with you. I hope you can give us a reference and support the help house.