--获取父节点下的所有子节点
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetChild]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetChild]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
Create function [dbo].[GetChild](@ID varchar(10))
returns @t table(ID varchar(10),ParentID varchar(10),Level int)
as
begin
declare @i int
set @i = 1
insert into @t select @ID,@ID,0 --当前级，本级，如果不要的话可以注释掉或再加个参数来选择操作
insert into @t select ID,ParentID,@i from Dept where ParentID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.ParentID,@i
from
Dept a,@t b
where
a.ParentID=b.ID and b.Level = @i-1
end
return
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
//--------例如----------//
--------表Dept ----------
--ID ParentID--
--1 0--
--2 1--
--3 1--
--4 2--
--5 3--
--6 5--
select ID from GetChild(3)
--ID ParentID Level
--3 3 0
--5 3 1
--6 5 2
----------------------------------------------------------------------------------------------------------------------------
--获取子节点下的所有父节点
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fnGetFather](@id varchar(50))
returns @t table(ID varchar(50),ParentID varchar(50),Depth int)
as
begin
insert into @t select ID,ParentID,@id from Dept where ID = @id
select @id = ParentID from Dept where ID = @id
while @@rowcount > 0
begin
insert into @t select ID,ParentID,@id from Dept where ID = @id
select @id = ParentID from Dept where ID = @id
end
return
end
GO
