SQL Server implementation tree structure recursive query (infinite Pole classification)

Starting with SQL Server 2005, we can support recursive queries directly through a CTE, which is a common table expression

Baidu Encyclopedia

A common table expression (CTE), which is a temporary named result set that is defined in a query, is used in the FROM clause. Each CTE is defined only once (but can be referenced any time within its scope) and will survive for the duration of the query. You can use a CTE to perform recursive operations. The syntax created is:

With <name of cte> (<column names>)

As (<actual query>)

SELECT * from <name of your cte>

1. Generate Data

--Menu directory Structure table CREATE TABLE Tb_menu (ID int NOT NULL,--primary Key ID title varchar (50),--Header parent int--parent ID);  --parent menu 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);  --First level menu INSERT into Tb_menu (ID, title, parent) VALUES (6, ' level menu 6 ', 1);  INSERT into Tb_menu (ID, title, parent) VALUES (7, ' level menu 7 ', 1);  INSERT into Tb_menu (ID, title, parent) VALUES (8, ' level menu 8 ', 1);  INSERT into Tb_menu (ID, title, parent) VALUES (9, ' level menu 9 ', 2);  INSERT into Tb_menu (ID, title, parent) VALUES (10, ' level menu 10 ', 2);  INSERT into Tb_menu (ID, title, parent) VALUES (11, ' level menu 11 ', 2);  INSERT into Tb_menu (ID, title, parent) VALUES (12, ' level menu 12 ', 3);  INSERT into Tb_menu (ID, title, parent) VALUES (13, ' level menu 13 ', 3); INSERT into Tb_menu (ID, title, parent) VALUES (14, ' level menu 14 ', 3);  INSERT into Tb_menu (ID, title, parent) VALUES (15, ' level menu 15 ', 4);  INSERT into Tb_menu (ID, title, parent) VALUES (16, ' level menu 16 ', 4);  INSERT into Tb_menu (ID, title, parent) VALUES (17, ' level menu 17 ', 4);  INSERT into Tb_menu (ID, title, parent) VALUES (18, ' level menu 18 ', 5);  INSERT into Tb_menu (ID, title, parent) VALUES (19, ' level menu 19 ', 5);  INSERT into Tb_menu (ID, title, parent) VALUES (20, ' level menu 20 ', 5);  --Level Two menu insert into Tb_menu (ID, title, parent) VALUES (21, ' Level two menu 21 ', 6);  INSERT into Tb_menu (ID, title, parent) VALUES (22, ' Level two menu 22 ', 6);  INSERT into Tb_menu (ID, title, parent) VALUES (23, ' Level two menu 23 ', 7);  INSERT into Tb_menu (ID, title, parent) VALUES (24, ' Level two menu 24 ', 7);  INSERT into Tb_menu (ID, title, parent) VALUES (25, ' Level two menu 25 ', 8);  INSERT into Tb_menu (ID, title, parent) VALUES (26, ' Level two menu 26 ', 9);  INSERT into Tb_menu (ID, title, parent) VALUES (27, ' Level two menu 27 ', 10);  INSERT into Tb_menu (ID, title, parent) VALUES (28, ' Level two menu 28 ', 11);  INSERT into Tb_menu (ID, title, parent) VALUES (29, ' Level two menu 29 ', 12); INSERT INTOTb_menu (ID, title, parent) VALUES (30, ' Level two menu 30 ', 13);  INSERT into Tb_menu (ID, title, parent) VALUES (31, ' Level two menu 31 ', 14);  INSERT into Tb_menu (ID, title, parent) VALUES (32, ' Level two menu 32 ', 15);  INSERT into Tb_menu (ID, title, parent) VALUES (33, ' Level two menu 33 ', 16);  INSERT into Tb_menu (ID, title, parent) VALUES (34, ' Level two menu 34 ', 17);  INSERT into Tb_menu (ID, title, parent) VALUES (35, ' Level two menu 35 ', 18);  INSERT into Tb_menu (ID, title, parent) VALUES (36, ' Level two menu 36 ', 19);    INSERT into Tb_menu (ID, title, parent) VALUES (37, ' Level two menu 37 ', 20);  --Level Three menu insert into Tb_menu (ID, title, parent) VALUES (38, ' Level three menu 38 ', 21);  INSERT into Tb_menu (ID, title, parent) VALUES (39, ' Level three menu 39 ', 22);  INSERT into Tb_menu (ID, title, parent) VALUES (40, ' Level three menu 40 ', 23);  INSERT into Tb_menu (ID, title, parent) VALUES (41, ' Level three menu 41 ', 24);  INSERT into Tb_menu (ID, title, parent) VALUES (42, ' Level three menu 42 ', 25);  INSERT into Tb_menu (ID, title, parent) VALUES (43, ' Level three menu 43 ', 26);  INSERT into Tb_menu (ID, title, parent) VALUES (44, ' Level three menu 44 ', 27); INSERT into Tb_menu (ID, titLe, parent) VALUES (45, ' Level three menu 45 ', 28);  INSERT into Tb_menu (ID, title, parent) VALUES (46, ' Level three menu 46 ', 28);  INSERT into Tb_menu (ID, title, parent) VALUES (47, ' Level three menu 47 ', 29);  INSERT into Tb_menu (ID, title, parent) VALUES (48, ' Level three menu 48 ', 30);  INSERT into Tb_menu (ID, title, parent) VALUES (49, ' Level three menu 49 ', 31);  INSERT into Tb_menu (ID, title, parent) VALUES (50, ' Level three menu 50 ', 31);   Commit

2. Find all ancestor nodes

--Query the ancestor of the tree structure node (44) All root nodes with    cte_parent (id,title,parent) as    (        --Start condition        Select Id,title,parent From        tb_menu        where id = $-   -List child nodes query condition        UNION ALL        --Recursive condition        Select A.id,a.title,a.parent        From Tb_menu a        inner join         cte_parent B          --perform recursion, here is to understand the down         a.id=b.parent      )                           select * FROM Cte_parent;  

3. Find subordinate nodes with level

--Query The tree structure all child nodes under a node (with    cte_child (id,title,parent,level)    as    (        --Starting condition        Select Id,title, parent,0 as level from        tb_menu        where id = 6--List parent node query condition        UNION ALL        --Recursive condition        Select A.id,a.title, A.parent,b.level+1 from        Tb_menu a        inner joins         Cte_child B on        (a.parent=b.id)      )    Select  * from Cte_child;  

SQL Server implementation tree structure recursive query (infinite Pole classification)