共计 4136 个字符，预计需要花费 11 分钟才能阅读完成。
本篇文章为大家展示了 sqlserver 中怎么实现树形结构递归查询，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。
公用表表达式（CTE），是一个在查询中定义的临时命名结果集将在 from 子句中使用它。每个 CTE 仅被定义一次（但在其作用域内可以被引用任意次），并且在该查询生存期间将一直生存。可以使用 CTE 来执行递归操作。创建的语法是：
with name of you cte (column names)as(actual query)select * from name of your cte1、生成数据
-- 菜单目录结构表 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. 查找所有上级节点
-- 查询树状结构某节点 (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
-- 查询树状结构某节点下的所有子节点( 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 中怎么实现树形结构递归查询，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注丸趣 TV 行业资讯频道。
正文完
