SQL Server 2005开始，我们可以直接通过CTE来支持递归查询,CTE即公用表表达式

百度百科

公用表表达式（CTE），是一个在查询中定义的临时命名结果集将在from子句中使用它。每个CTE仅被定义一次（但在其作用域内可以被引用任意次），并且在该查询生存期间将一直生存。可以使用CTE来执行递归操作。创建的语法是：

with <name of you cte>(<column names>)

as(

<actual query>

)

select * from <name of your cte>

1. 生成数据
   

--菜单目录结构表
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,?parent)?values(2,?'父菜单2',?parent)?values(3,?'父菜单3',?parent)?values(4,?'父菜单4',?parent)?values(5,?'父菜单5',null);
--一级菜单
insert?into?tb_menu(id,?parent)?values(6,?'一级菜单6',1);
insert?into?tb_menu(id,?parent)?values(7,?'一级菜单7',?parent)?values(8,?'一级菜单8',?parent)?values(9,?'一级菜单9',2);
insert?into?tb_menu(id,?parent)?values(10,?'一级菜单10',?parent)?values(11,?'一级菜单11',?parent)?values(12,?'一级菜单12',3);
insert?into?tb_menu(id,?parent)?values(13,?'一级菜单13',?parent)?values(14,?'一级菜单14',?parent)?values(15,?'一级菜单15',4);
insert?into?tb_menu(id,?parent)?values(16,?'一级菜单16',?parent)?values(17,?'一级菜单17',?parent)?values(18,?'一级菜单18',5);
insert?into?tb_menu(id,?parent)?values(19,?'一级菜单19',?parent)?values(20,?'一级菜单20',5);
--二级菜单
insert?into?tb_menu(id,?parent)?values(21,?'二级菜单21',6);
insert?into?tb_menu(id,?parent)?values(22,?'二级菜单22',?parent)?values(23,?'二级菜单23',7);
insert?into?tb_menu(id,?parent)?values(24,?'二级菜单24',?parent)?values(25,?'二级菜单25',8);
insert?into?tb_menu(id,?parent)?values(26,?'二级菜单26',9);
insert?into?tb_menu(id,?parent)?values(27,?'二级菜单27',10);
insert?into?tb_menu(id,?parent)?values(28,?'二级菜单28',11);
insert?into?tb_menu(id,?parent)?values(29,?'二级菜单29',12);
insert?into?tb_menu(id,?parent)?values(30,?'二级菜单30',13);
insert?into?tb_menu(id,?parent)?values(31,?'二级菜单31',14);
insert?into?tb_menu(id,?parent)?values(32,?'二级菜单32',15);
insert?into?tb_menu(id,?parent)?values(33,?'二级菜单33',16);
insert?into?tb_menu(id,?parent)?values(34,?'二级菜单34',17);
insert?into?tb_menu(id,?parent)?values(35,?'二级菜单35',18);
insert?into?tb_menu(id,?parent)?values(36,?'二级菜单36',19);
insert?into?tb_menu(id,?parent)?values(37,?'二级菜单37',20);

--三级菜单
insert?into?tb_menu(id,?parent)?values(38,?'三级菜单38',21);
insert?into?tb_menu(id,?parent)?values(39,?'三级菜单39',22);
insert?into?tb_menu(id,?parent)?values(40,?'三级菜单40',23);
insert?into?tb_menu(id,?parent)?values(41,?'三级菜单41',24);
insert?into?tb_menu(id,?parent)?values(42,?'三级菜单42',25);
insert?into?tb_menu(id,?parent)?values(43,?'三级菜单43',26);
insert?into?tb_menu(id,?parent)?values(44,?'三级菜单44',27);
insert?into?tb_menu(id,?parent)?values(45,?'三级菜单45',28);
insert?into?tb_menu(id,?parent)?values(46,?'三级菜单46',?parent)?values(47,?'三级菜单47',29);
insert?into?tb_menu(id,?parent)?values(48,?'三级菜单48',30);
insert?into?tb_menu(id,?parent)?values(49,?'三级菜单49',31);
insert?into?tb_menu(id,?parent)?values(50,?'三级菜单50',31);
commit;

2.查找所有上级节点

--查询树状结构某节点(44)的上级所有根节点

with?cte_parent(id,title,parent)

as

(

????--起始条件

????select?id,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,parent,level)

as

(

????--起始条件

????select?id,0?as?level

????from?tb_menu

????where?id?=?6--列出父节点查询条件

????union?all

????--递归条件

????select?a.id,a.parent,b.level+1

????from?tb_menu?a

????inner?join?

????cte_child?b

????on?(?a.parent=b.id)??

)

select??*?from?cte_child;

参考文章

SQL Server 2008中的CTE递归查询 http://www.cnblogs.com/xfrog/archive/2010/10/10/1847462.html

Oracle 树操作(start with…connect by…prior)? http://www.cnblogs.com/linjiqin/archive/2013/06/24/3152674.html

“无限极”分类数据表设计的简单再总结 http://www.cnblogs.com/jeffwongishandsome/archive/2010/10/26/1861633.html

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!