树节点查询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