求雇员的直接上级和间接上级

--测试代码

--创建表
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

*/