函数:create function fn_count4(
@用户名 varchar(20)
)
returns int
as
begin
declare @i int
set @i=1
declare @t table (
用户名 varchar(20),
lev int
)
insert @t values(@用户名,0)
while @i<=4 and exists (
select 1 from tablename
where 推荐用户名 in (select 用户名 from @t where lev=@i-1)
and 用户名 not in (select 用户名 from @t)
)
begin
insert @t
select 用户名,@i from tablename
where 推荐用户名 in (select 用户名 from @t where lev=@i-1)
and 用户名 not in (select 用户名 from @t)
set @i=@i+1
end
return (select count(*) from @t where 用户名<>@用户名)endgo--调用
select dbo.fn_count4('a')
@用户名 varchar(20)
)
returns int
as
begin
declare @i int
set @i=1
declare @t table (
用户名 varchar(20),
lev int
)
insert @t values(@用户名,0)
while @i<=4 and exists (
select 1 from tablename
where 推荐用户名 in (select 用户名 from @t where lev=@i-1)
and 用户名 not in (select 用户名 from @t)
)
begin
insert @t
select 用户名,@i from tablename
where 推荐用户名 in (select 用户名 from @t where lev=@i-1)
and 用户名 not in (select 用户名 from @t)
set @i=@i+1
end
return (select count(*) from @t where 用户名<>@用户名)endgo--调用
select dbo.fn_count4('a')
declare @t table (用户名 varchar(10),推荐用户名 varchar(10))
insert @t
select 'a','' union all
select 'b','a' union all
select 'c','b' union all
select 'd','c' union all
select 'e','d' union all
select 'f','b'
----查找所有下级
declare @t2 table(用户名 varchar(10)) --保存子结点
declare @id varchar(10) --父节点名称
set @id = 'a'
insert into @t2 select 用户名 from @t where 推荐用户名 = @id
WHILE @@ROWCOUNT>0
insert into @t2
select a.用户名 from @t a inner join @t2 b on a.推荐用户名 = b.用户名
where a.用户名 not in(select 用户名 from @t2)
----查看下级数量
select count(*) from @t2
create proc next_five_total
as
begin
declare @i int
declare @t int
select @t=count(*) from info_new
set @i=1
while @i<=@t
begin
declare @tb table(nameid varchar(20),icount int)
declare @tuijian_id varchar(20)
select @tuijian_id=nameid from info_new where id=@i
declare @count int
select @count=count(*) from (
select nameid,tuijian_id from info_new where tuijian_id=@tuijian_id
union all
select nameid,tuijian_id from info_new where tuijian_id in(select nameid from info_new where tuijian_id=@tuijian_id)
union all
select nameid,tuijian_id from info_new where tuijian_id in(select nameid from info_new where tuijian_id in(
select nameid from info_new where tuijian_id=@tuijian_id))
union all
select nameid,tuijian_id from info_new where tuijian_id in(select nameid from info_new where tuijian_id in(
select nameid from info_new where tuijian_id in(select nameid from info_new where tuijian_id=@tuijian_id)))
union all
select nameid,tuijian_id from info_new where tuijian_id in(select nameid from info_new where tuijian_id in(
select nameid from info_new where tuijian_id in(select nameid from info_new where tuijian_id in(
select nameid from info_new where tuijian_id=@tuijian_id))))) a
insert @tb select @tuijian_id,@count
set @i=@i+1
end
select distinct * into #top_name from @tb order by icount desc
end
go
select * from #top_name
你的思路好像有点问题,用子查询很影响速度的,而且这样5层的子查询重复查询的太多,用表变量的目的就是减少重复的查询
我对那些什么临时表什么的都不会
谁给个好用的我用户名 推荐用户名
a
b a
c b
d c
e d
f b统计a的下4层用户有多少人?我要的结果是,比如我要找b下面四层有多少人
用户名 下4层总人数
b 4