sqlserver實(shí)現(xiàn)樹形結(jié)構(gòu)遞歸查詢(無限極分類)的方法
發(fā)布日期：2021-12-13 18:48 | 文章來源：CSDN
SQL Server 2005開始，我們可以直接通過CTE來支持遞歸查詢,CTE即公用表表達(dá)式
百度百科
公用表表達(dá)式（CTE），是一個(gè)在查詢中定義的臨時(shí)命名結(jié)果集將在from子句中使用它。每個(gè)CTE僅被定義一次（但在其作用域內(nèi)可以被引用任意次），并且在該查詢生存期間將一直生存?？梢允褂肅TE來執(zhí)行遞歸操作。創(chuàng)建的語法是：
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); --一級菜單 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é)點(diǎn)
--查詢樹狀結(jié)構(gòu)某節(jié)點(diǎn)(44)的上級所有根節(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.查找下級節(jié)點(diǎn)帶level
--查詢樹狀結(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)樹形結(jié)構(gòu)遞歸查詢(無限極分類)的方法就是小編分享給大家的全部內(nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持本站。
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信