SQL实现递归算法

样表：

child,parent

1     0

2     0

3     1

4     2

要实现这种用法一般都通过两种方式来实现：

procedure 方式：

create procedure usp_getallchild(@child int)

as

declare @t table(child int null,parent int null,level intnull)

declare @level int

set @level=0

insert into @t(child,parent,level) select child,parent,@level fromtable where child=@child

while @@rowcount>0

begin

 set @level=@level+1

 insert into @t(child,parent,level) selectchild,parent,@level from table jion @t as t on t.child=table.parentwhere t.level=@level-1

end

select * from @t

function 方式：

create function udf_getallchild(@child int)

returns @t_return table(child int null,parent int null,level intnull)

as

begin

 declare @level int

 set @level=0

 insert into @t_return(child,parent,level)select child,parent,@level from table where child=@child

 while @@rowcount>0

 begin

  set@level=@level+1

  insert into@t(child,parent,level) select child,parent,@level from table jion@t_return as t on t.child=table.parent where t.level=@level-1

 end

       return @t_return

end

-- =============================================

-- Author:

      Gibil

-- Create date: 2011-9-24

--Description:   获取指定代理的所有子类方法

-- =============================================

Create FUNCTION GetAllSubAgent

(

    @AgentIDvarchar(20)

)

RETURNS @t_return TABLE(C_ID int IDENTITY (1,1) NOT NULL PRIMARYKEY,C_AgentID varchar(20) NOT NULL,C_UserName varchar(50)NULL,C_Level int NOT NULL)

AS

BEGIN

    DECLARE@Level int

    SET @Level =0

    INSERT INTO@t_return(C_AgentID,C_UserName,C_Level) SELECT@AgentID,C_UserName,@Level FROM T_UserBase AS U INNER JOIN

      T_PropertyType AS P ON U.[C_PropertyTypeID] = P.[C_TypeID] WHEREU.[C_AccountID] = @AgentID AND P.[C_UserType] = 2 --插入指定代理

    WHILE@@rowcount > 0 --上次执行的受影响行数大于0

    BEGIN

       SET @Level =@Level + 1

       INSERT INTO@t_return(C_AgentID,C_UserName,C_Level) SELECTC_AccountID,C_UserName,@Level FROM T_UserBase AS U

          INNER JOINT_PropertyType AS P ON U.[C_PropertyTypeID] = P.[C_TypeID] WHEREU.[C

摘自 wjbaiverson的专栏