树节点查询SQL【递归】

通用树状节点获取所有叶节点和支节点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_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
   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 
return
end
go
select * from dbo.Uf_GetChildID(5)

 

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

if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID
go
create function Uf_GetParentID(@ID int)
returns @t table(ParentID int)
as
begin
   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
  return
end
go
select * from dbo.Uf_GetParentID(2)

 

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