declare @userid int; --推广员帐号
declare @proxyid int; --代理帐号
declare @score int=1000; --分数
select
@userid = [spreaderid]
from
[qpaccountsdb].[dbo].[accountsinfo]
where
userid = 5055;
select --查出推广员的代理帐号
@proxyid = proxyid
from
[qpaccountsdb].[dbo].[accountsinfo]
left join
[qpproxydb].[dbo].[bs_proxyinfo]
on bs_proxyinfo.account = accountsinfo.accounts
where
userid = @userid;
print @proxyid;
create table #proxyinfo
(
belongsagent int,
assignproportion tinyint
);
with cte
as ( select
belongsagent
from
[qpproxydb].[dbo].[bs_proxyinfo]
where
proxyid = @proxyid
and belongsagent <> -1
union all
select
a.belongsagent
from
[qpproxydb].[dbo].[bs_proxyinfo] a
join
cte b
on a.proxyid = b.belongsagent
where
a.belongsagent <> -1)
insert #proxyinfo
(
belongsagent,
assignproportion
)
select
bs_proxyinfo.proxyid,
assignproportion
from
cte left join [qpproxydb].[dbo].[bs_proxyinfo] on bs_proxyinfo.proxyid = cte.belongsagent
order by
bs_proxyinfo.belongsagent asc;
---游标更新删除当前数据
---1.声明游标
declare cursor01 cursor scroll for
select
*
from
#proxyinfo
order by
belongsagent asc;
declare @alltax int
set @alltax =@score
--2.打开游标
open cursor01;
--3.声明游标提取数据所要存放的变量
declare
@belongsagent int,
@assignproportion tinyint;
--4.定位游标到哪一行
fetch first from cursor01
into
@belongsagent,
@assignproportion; --into的变量数量必须与游标查询结果集的列数相同
while @@fetch_status = 0 --提取成功，进行下一条数据的提取操作
begin
set @alltax=@assignproportion*@alltax/100
update [qpproxydb].[dbo].[bs_proxyinfo] set alltax+=@alltax where proxyid=@belongsagent
fetch next from cursor01
into
@belongsagent,
@assignproportion; --移动游标
end;
close cursor01;
deallocate cursor01;
drop table #proxyinfo;
