--楼主,用函数 --测试代码--创建表 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*/
--楼主,用函数 --测试代码--创建表 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*/
--上面的代码多了两句,用这个就可以了--楼主,用函数 --测试代码--创建表 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*/
--测试代码--创建表
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*/
--测试代码--创建表
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*/
--测试代码--创建表
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*/