不一定5级用个函数实现
create function fn_GetInvitedCount(
@InviterID varchar(20)
)
returns int
as
begin
declare @t table (UserId varchar(20))
insert @t select UserId from A where InviterID=@InviterID
while exists (select 1 from A
where UserId not in (select UserId from @t)
and InviterID in (select UserId from @t)
)
insert @r
select UserId from A
where UserId not in (select UserId from @t)
and InviterID in (select UserId from @t) declare @r int
set @r=0
select @r=count(*) from @t
return @r
end
go--调用
select dbo.fn_fn_GetInvitedCount('inviter1')
create function fn_GetInvitedCount(
@InviterID varchar(20)
)
returns int
as
begin
declare @t table (UserId varchar(20))
insert @t select UserId from A where InviterID=@InviterID
while exists (select 1 from A
where UserId not in (select UserId from @t)
and InviterID in (select UserId from @t)
)
insert @r
select UserId from A
where UserId not in (select UserId from @t)
and InviterID in (select UserId from @t) declare @r int
set @r=0
select @r=count(*) from @t
return @r
end
go--调用
select dbo.fn_fn_GetInvitedCount('inviter1')
解决方案 »
- 有两个表,一对多的关系,我想把第二个表中与第一个表对应的记录合并为一个字段fieldNew,然后 select a.field1, fieldNew from a,怎么办?
- 为了满足2.5范式 一个表的外键应该控制在几个
- 请教一个简单的SQL语句
- 查询当天数据??
- SQL大表内连接优化
- SQL苦手跪求帮助
- 数据库的容量限制和客户授权问题
- 安装SQL Server2000“选择许可模式”出错:由于系统缓冲区空间不足或列队已满
- 有两个数据库A和B,其中都有表C,如何把库B中的表C的数据,复制到库A中的表C,表中有自增长字段,谢谢
- 这个问题你会吗?
- SQL2000链接Ora表,查询时候的错误!
- 【救命】sqlserver2005的分析数据库部署成功后最终的展示结果是通过什么浏览??在客户端如何查看服务器端做的分析表?
from a x
where not exists (
select 1 from a
where userId=x.InviterID
)
group by InviterID
u1 i1
u2 u1
u3 i1
u4 u3
u5 u4@t
userid inviterid
u1 i1
u3 i1这样的话u5就统计不到了吧?
不知道可以不可以写自调用的函数来实现? 不过sql里俺不会写.
InviterID int,
Generation int
)declare
@Generation int,
@UserID int
set @UserID='要查寻的ID'
set @Generation=0
insert #user(userID ,InviterID,Generation)
select @userID,InviterID,@Generation from 存储数据的表
WHILE @Generation<5
Begin
SET @Generation = @Generation + 1
Insert #user(userID,InviterID,Generation)
select m.userID
m.InviterID
@Generation
from 存储数据的表 as m join #user
on #user.Generation=@Generation-1
and #user.usrID=m.InviterIDEnd