--测试代码
--创建表
create table tb(userid int,username varchar(50),parentid int)
insert tb select 1001,'rose',null union all
select 1002,'will',1001 union all
select 1003,'yao',1002 union all
select 1004,'gigi',1002 union all
select 1005,'frank',1004
--创建函数
--根据用户的ID号,列出用户的全部上线
CREATE function Parent_List(
@userID int
)returns @re table(userID int,level int)
as
begin
declare @l int
set @l=0
insert @re select @userID,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.parentid,@l
from [tb] a,@re b
where a.userid=b.userID and b.level=@l-1
end
return
end
--查询,parent_list里的参数,可以根据你的需求来定
select b.* from dbo.parent_list(1005) as a inner join tb as b on a.userid = b.userid
/*
测试结果
userid username parentid
1001rose NULL
1002will 1001
1004gigi 1002
1005frank 1004
*/