SQL Server 2005开始,我们可以直接通过CTE来支持递归查询,CTE即公用表表达式
百度百科
公用表表达式(CTE),是一个在查询中定义的临时命名结果集将在from子句中使用它。每个CTE仅被定义一次(但在其作用域内可以被引用任意次),并且在该查询生存期间将一直生存。可以使用CTE来执行递归操作。创建的语法是:
1
2
3
4
5
|
with < name of you cte>(< column names>) as ( <actual query> ) select * from < name of your cte> |
1、生成数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
|
--菜单目录结构表 create table tb_menu( id int not null , --主键id title varchar (50), --标题 parent int --parent id ); --父菜单 insert into tb_menu(id, title, parent) values (1, '父菜单1' , null ); insert into tb_menu(id, title, parent) values (2, '父菜单2' , null ); insert into tb_menu(id, title, parent) values (3, '父菜单3' , null ); insert into tb_menu(id, title, parent) values (4, '父菜单4' , null ); insert into tb_menu(id, title, parent) values (5, '父菜单5' , null ); --一级菜单 insert into tb_menu(id, title, parent) values (6, '一级菜单6' ,1); insert into tb_menu(id, title, parent) values (7, '一级菜单7' ,1); insert into tb_menu(id, title, parent) values (8, '一级菜单8' ,1); insert into tb_menu(id, title, parent) values (9, '一级菜单9' ,2); insert into tb_menu(id, title, parent) values (10, '一级菜单10' ,2); insert into tb_menu(id, title, parent) values (11, '一级菜单11' ,2); insert into tb_menu(id, title, parent) values (12, '一级菜单12' ,3); insert into tb_menu(id, title, parent) values (13, '一级菜单13' ,3); insert into tb_menu(id, title, parent) values (14, '一级菜单14' ,3); insert into tb_menu(id, title, parent) values (15, '一级菜单15' ,4); insert into tb_menu(id, title, parent) values (16, '一级菜单16' ,4); insert into tb_menu(id, title, parent) values (17, '一级菜单17' ,4); insert into tb_menu(id, title, parent) values (18, '一级菜单18' ,5); insert into tb_menu(id, title, parent) values (19, '一级菜单19' ,5); insert into tb_menu(id, title, parent) values (20, '一级菜单20' ,5); --二级菜单 insert into tb_menu(id, title, parent) values (21, '二级菜单21' ,6); insert into tb_menu(id, title, parent) values (22, '二级菜单22' ,6); insert into tb_menu(id, title, parent) values (23, '二级菜单23' ,7); insert into tb_menu(id, title, parent) values (24, '二级菜单24' ,7); insert into tb_menu(id, title, parent) values (25, '二级菜单25' ,8); insert into tb_menu(id, title, parent) values (26, '二级菜单26' ,9); insert into tb_menu(id, title, parent) values (27, '二级菜单27' ,10); insert into tb_menu(id, title, parent) values (28, '二级菜单28' ,11); insert into tb_menu(id, title, parent) values (29, '二级菜单29' ,12); insert into tb_menu(id, title, parent) values (30, '二级菜单30' ,13); insert into tb_menu(id, title, parent) values (31, '二级菜单31' ,14); insert into tb_menu(id, title, parent) values (32, '二级菜单32' ,15); insert into tb_menu(id, title, parent) values (33, '二级菜单33' ,16); insert into tb_menu(id, title, parent) values (34, '二级菜单34' ,17); insert into tb_menu(id, title, parent) values (35, '二级菜单35' ,18); insert into tb_menu(id, title, parent) values (36, '二级菜单36' ,19); insert into tb_menu(id, title, parent) values (37, '二级菜单37' ,20); --三级菜单 insert into tb_menu(id, title, parent) values (38, '三级菜单38' ,21); insert into tb_menu(id, title, parent) values (39, '三级菜单39' ,22); insert into tb_menu(id, title, parent) values (40, '三级菜单40' ,23); insert into tb_menu(id, title, parent) values (41, '三级菜单41' ,24); insert into tb_menu(id, title, parent) values (42, '三级菜单42' ,25); insert into tb_menu(id, title, parent) values (43, '三级菜单43' ,26); insert into tb_menu(id, title, parent) values (44, '三级菜单44' ,27); insert into tb_menu(id, title, parent) values (45, '三级菜单45' ,28); insert into tb_menu(id, title, parent) values (46, '三级菜单46' ,28); insert into tb_menu(id, title, parent) values (47, '三级菜单47' ,29); insert into tb_menu(id, title, parent) values (48, '三级菜单48' ,30); insert into tb_menu(id, title, parent) values (49, '三级菜单49' ,31); insert into tb_menu(id, title, parent) values (50, '三级菜单50' ,31); commit ; |
2.查找所有上级节点
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
--查询树状结构某节点(44)的上级所有根节点 with cte_parent(id,title,parent) as ( --起始条件 select id,title,parent from tb_menu where id = 44 --列出子节点查询条件 union all --递归条件 select a.id,a.title,a.parent from tb_menu a inner join cte_parent b --执行递归,这里就要理解下了 on a.id=b.parent ) select * from cte_parent; |
3.查找下级节点带level
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
--查询树状结构某节点下的所有子节点( with cte_child(id,title,parent, level ) as ( --起始条件 select id,title,parent,0 as level from tb_menu where id = 6 --列出父节点查询条件 union all --递归条件 select a.id,a.title,a.parent,b. level +1 from tb_menu a inner join cte_child b on ( a.parent=b.id) ) select * from cte_child; |
以上这篇sqlserver实现树形结构递归查询(无限极分类)的方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持服务器之家。