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;