您的位置:澳门新葡8455最新网站 > 数据库管理 > 澳门新葡萄京娱乐场游标和递归sql

澳门新葡萄京娱乐场游标和递归sql

发布时间:2019-12-09 15:19编辑:数据库管理浏览(134)

    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;
    

     

    本文由澳门新葡8455最新网站发布于数据库管理,转载请注明出处:澳门新葡萄京娱乐场游标和递归sql

    关键词: