样表：
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的专栏