SQL Server 2005開始，我們可以直接通過CTE來支持遞歸查詢,CTE即公用表表達式

?百度百科?公用表表達式（CTE），是一個在查詢中定義的臨時命名結(jié)果集將在from子句中使用它。每個CTE僅被定義一次（但在其作用域內(nèi)可以被引用任意次），并且在該查詢生存期間將一直生存?？梢允褂肅TE來執(zhí)行遞歸操作。創(chuàng)建的語法是：with <name of you cte>(<column names>)as(<actual query>)select * from <name of your cte>?生成數(shù)據(jù)?--菜單目錄結(jié)構(gòu)表create table tb_menu(?id int not null, --主鍵idtitle 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.查找所有上級節(jié)點?--查詢樹狀結(jié)構(gòu)某節(jié)點(44)的上級所有根節(jié)點?with cte_parent(id,title,parent)?as?(?? ? --起始條件?? ? select id,title,parent?? ? from tb_menu?? ? where id = 44 ? --列出子節(jié)點查詢條件?? ? union all?? ? --遞歸條件?? ? select a.id,a.title,a.parent?? ? from tb_menu a?? ? inner join??? ? cte_parent b ? ? ? ? ?--執(zhí)行遞歸，這里就要理解下了??? ? on a.id=b.parent ??) ? ? ? ? ? ? ? ? ? ? ???select * from cte_parent;??3.查找下級節(jié)點帶level??--查詢樹狀結(jié)構(gòu)某節(jié)點下的所有子節(jié)點(?with cte_child(id,title,parent,level)?as?(?? ? --起始條件?? ? select id,title,parent,0 as level?? ? from tb_menu?? ? where id = 6--列出父節(jié)點查詢條件?? ? 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;