快捷導(dǎo)航

sqlserver實(shí)現(xiàn)樹(shù)形結(jié)構(gòu)遞歸查詢(無(wú)限極分類(lèi))的方法

更新時(shí)間：2017年07月03日 15:43:29 投稿：jingxian

下面小編就為大家?guī)?lái)一篇sqlserver實(shí)現(xiàn)樹(shù)形結(jié)構(gòu)遞歸查詢(無(wú)限極分類(lèi))的方法。小編覺(jué)得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧

SQL Server 2005開(kāi)始，我們可以直接通過(guò)CTE來(lái)支持遞歸查詢,CTE即公用表表達(dá)式

百度百科

公用表表達(dá)式（CTE），是一個(gè)在查詢中定義的臨時(shí)命名結(jié)果集將在from子句中使用它。每個(gè)CTE僅被定義一次（但在其作用域內(nèi)可以被引用任意次），并且在該查詢生存期間將一直生存。可以使用CTE來(lái)執(zhí)行遞歸操作。創(chuàng)建的語(yǔ)法是：

with <name of you cte>(<column names>)
as(
<actual query>
)
select * from <name of your cte>

1、生成數(shù)據(jù)

--菜單目錄結(jié)構(gòu)表 
create table tb_menu( 
 id int not null, --主鍵id 
title varchar(50), --標(biāo)題 
 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); 
--一級(jí)菜單 
insert into tb_menu(id, title, parent) values(6, '一級(jí)菜單6',1); 
insert into tb_menu(id, title, parent) values(7, '一級(jí)菜單7',1); 
insert into tb_menu(id, title, parent) values(8, '一級(jí)菜單8',1); 
insert into tb_menu(id, title, parent) values(9, '一級(jí)菜單9',2); 
insert into tb_menu(id, title, parent) values(10, '一級(jí)菜單10',2); 
insert into tb_menu(id, title, parent) values(11, '一級(jí)菜單11',2); 
insert into tb_menu(id, title, parent) values(12, '一級(jí)菜單12',3); 
insert into tb_menu(id, title, parent) values(13, '一級(jí)菜單13',3); 
insert into tb_menu(id, title, parent) values(14, '一級(jí)菜單14',3); 
insert into tb_menu(id, title, parent) values(15, '一級(jí)菜單15',4); 
insert into tb_menu(id, title, parent) values(16, '一級(jí)菜單16',4); 
insert into tb_menu(id, title, parent) values(17, '一級(jí)菜單17',4); 
insert into tb_menu(id, title, parent) values(18, '一級(jí)菜單18',5); 
insert into tb_menu(id, title, parent) values(19, '一級(jí)菜單19',5); 
insert into tb_menu(id, title, parent) values(20, '一級(jí)菜單20',5); 
--二級(jí)菜單 
insert into tb_menu(id, title, parent) values(21, '二級(jí)菜單21',6); 
insert into tb_menu(id, title, parent) values(22, '二級(jí)菜單22',6); 
insert into tb_menu(id, title, parent) values(23, '二級(jí)菜單23',7); 
insert into tb_menu(id, title, parent) values(24, '二級(jí)菜單24',7); 
insert into tb_menu(id, title, parent) values(25, '二級(jí)菜單25',8); 
insert into tb_menu(id, title, parent) values(26, '二級(jí)菜單26',9); 
insert into tb_menu(id, title, parent) values(27, '二級(jí)菜單27',10); 
insert into tb_menu(id, title, parent) values(28, '二級(jí)菜單28',11); 
insert into tb_menu(id, title, parent) values(29, '二級(jí)菜單29',12); 
insert into tb_menu(id, title, parent) values(30, '二級(jí)菜單30',13); 
insert into tb_menu(id, title, parent) values(31, '二級(jí)菜單31',14); 
insert into tb_menu(id, title, parent) values(32, '二級(jí)菜單32',15); 
insert into tb_menu(id, title, parent) values(33, '二級(jí)菜單33',16); 
insert into tb_menu(id, title, parent) values(34, '二級(jí)菜單34',17); 
insert into tb_menu(id, title, parent) values(35, '二級(jí)菜單35',18); 
insert into tb_menu(id, title, parent) values(36, '二級(jí)菜單36',19); 
insert into tb_menu(id, title, parent) values(37, '二級(jí)菜單37',20); 
 
--三級(jí)菜單 
insert into tb_menu(id, title, parent) values(38, '三級(jí)菜單38',21); 
insert into tb_menu(id, title, parent) values(39, '三級(jí)菜單39',22); 
insert into tb_menu(id, title, parent) values(40, '三級(jí)菜單40',23); 
insert into tb_menu(id, title, parent) values(41, '三級(jí)菜單41',24); 
insert into tb_menu(id, title, parent) values(42, '三級(jí)菜單42',25); 
insert into tb_menu(id, title, parent) values(43, '三級(jí)菜單43',26); 
insert into tb_menu(id, title, parent) values(44, '三級(jí)菜單44',27); 
insert into tb_menu(id, title, parent) values(45, '三級(jí)菜單45',28); 
insert into tb_menu(id, title, parent) values(46, '三級(jí)菜單46',28); 
insert into tb_menu(id, title, parent) values(47, '三級(jí)菜單47',29); 
insert into tb_menu(id, title, parent) values(48, '三級(jí)菜單48',30); 
insert into tb_menu(id, title, parent) values(49, '三級(jí)菜單49',31); 
insert into tb_menu(id, title, parent) values(50, '三級(jí)菜單50',31); 
commit;

2.查找所有上級(jí)節(jié)點(diǎn)

--查詢樹(shù)狀結(jié)構(gòu)某節(jié)點(diǎn)(44)的上級(jí)所有根節(jié)點(diǎn) 
 
with cte_parent(id,title,parent) 
 
as 
 
( 
 
  --起始條件 
 
  select id,title,parent 
 
  from tb_menu 
 
  where id = 44  --列出子節(jié)點(diǎn)查詢條件 
 
  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.查找下級(jí)節(jié)點(diǎn)帶level

--查詢樹(shù)狀結(jié)構(gòu)某節(jié)點(diǎn)下的所有子節(jié)點(diǎn)( 
 
with cte_child(id,title,parent,level) 
 
as 
 
( 
 
  --起始條件 
 
  select id,title,parent,0 as level 
 
  from tb_menu 
 
  where id = 6--列出父節(jié)點(diǎn)查詢條件 
 
  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實(shí)現(xiàn)樹(shù)形結(jié)構(gòu)遞歸查詢(無(wú)限極分類(lèi))的方法就是小編分享給大家的全部?jī)?nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: