如何求雇员的直接上级和间接上级？

[leckylee]

create table employee
(
eid int primary key,
ename varchar(10),
reportto int references employee(eid)
)
insert into employee select 1001,'rose',null
 union select 1002,'will',1001
 union select 1003,'yao',1002
 union select 1004,'gigi',1002
union select 1005,'frank',1004
reportto记录职员的直接上级

求出该表任意职员的所有直接上级以及间接上级，包括自身
例如frank的上级包括frank，gigi，will，rose

老师们麻烦你们了....

[leckylee]

怎么没人呀...

[danisluo]

相对应的表结构都没看到，别人怎么给你回复，总不能让别人死太多的脑细胞吧，楼主

[Aden]

select distinct a.eid,a.ename,a.reportto from employee as a ,employee as b where a.eid = b.reportto union select * from employee where ename = 'frank'

[zoubsky]

--楼主，用函数
--测试代码

--创建表
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
if @l > 4
return
insert @re select a.parentid,@l
from [tb] a,@re b
where a.userid=b.userID and b.level=@l-1


end
return
end


--查询
select b.* from dbo.parent_list(1005) as a inner join tb as b on a.userid = b.userid



/*
测试结果
userid username parentid
1001 rose NULL
1002 will 1001
1004 gigi 1002
1005 frank 1004

*/

[zoubsky]

--楼主，用函数
--测试代码

--创建表
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
if @l > 4
return
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
1001 rose NULL
1002 will 1001
1004 gigi 1002
1005 frank 1004

*/

[zoubsky]

--上面的代码多了两句，用这个就可以了



--楼主，用函数
--测试代码

--创建表
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
1001 rose NULL
1002 will 1001
1004 gigi 1002
1005 frank 1004

*/