树节点查询SQL【递归】

来源：互联网发布：淘宝宝贝详情视频代码编辑：程序博客网时间：2024/05/16 12:29

通用树状节点获取所有叶节点和支节点sql

--获取所有父节点的menu_item

select distinct s1_parent.*
from sys_menu s1_parent,sys_menu s2
where 1=1
and s1_parent.menu_id = s2.parent_id

--获取所有具体页面节点的menu_item

select s.*
from sys_menu s where s.menu_id not in
(select distinct s1_parent.menu_id
from sys_menu s1_parent,sys_menu s2
where 1=1
and s1_parent.menu_id = s2.parent_i

查找指定節點下的子結點：

if object_id('Uf_GetChildID')is not null drop function Uf_GetChildIDgocreate function Uf_GetChildID(@ParentID int)returns @t table(ID int)asbegin   insert @t select ID from tb where ParentID=@ParentID   while @@rowcount<>0   begin      insert @t select a.ID from tb a inner join @t b      on a.ParentID=b.id and       not exists(select 1 from @t where id=a.id)   end returnendgoselect * from dbo.Uf_GetChildID(5)

查找指定節點的所有父結點：

if object_id('Uf_GetParentID')is not null drop function Uf_GetParentIDgocreate function Uf_GetParentID(@ID int)returns @t table(ParentID int)asbegin   insert @t select ParentID from tb where ID=@ID   while @@rowcount!=0   begin     insert @t select a.ParentID from tb a inner join @t b       on a.id=b.ParentID and        not exists(select 1 from @t where ParentID=a.ParentID)   end  returnendgoselect * from dbo.Uf_GetParentID(2)

http://www.myexception.cn/sql-server/338889.html

0 0