SQL递归函数列出父级的所有子级(ID ParentID模式)

--调用方法：   

--select * from GetChild('24')   

--select id from GetChild('24')   

--select * from KuCun where ProductType in(select id from GetChild('24'))   

  

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  

  

--------------------------------------------------------------------------------   

--在SQL Server2005中其实提供了CTE[公共表表达式]来实现递归:    

Declare @Id Int    

Set @Id = 24;    ---在此修改父节点    

  

With RootNodeCTE(Id,ParentId)    

As    

(    

Select Id,ParentId From Dept Where ParentId In (@Id)    

Union All    

Select Dept.Id,Dept.ParentId From RootNodeCTE    

Inner Join Dept   

On RootNodeCTE.Id = Dept.ParentId    

)    

  

Select * From RootNodeCTE   

  

--------------------------------------------------------------------------------   

--------------------------------------------------------------------------------   

--------------------------------------------------------------------------------   

  

--生成测试数据   

create table Dept(ID int,ParentID int,msg varchar(20))   

insert into Dept select 1,0,null  

insert into Dept select 2,1,null  

insert into Dept select 3,1,null  

insert into Dept select 4,2,null  

insert into Dept select 5,3,null  

insert into Dept select 6,5,null  

insert into Dept select 7,6,null  

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  

  

  

--执行查询   

select ID from dbo.GetChild(3)   

go   

  

--输出结果   

/*   

3   

5   

6   

7   

*/   

  

--删除测试数据   

drop function GetChild   

drop table Dept

SQL递归函数列出父级的所有子级(ID ParentID模式) - 柳永法(yongfa365)'Blog
http://www.yongfa365.com/Item/SQL-Di-Gui-Function-ID-ParentID.html