SQL中获取子节点或父节点

-获取父节点下的所有子节点

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