游标和递归sql 的一些代码

来源:http://www.prospettivedarte.com 作者:计算机教程 人气:188 发布时间:2019-05-11
摘要:DECLARE @UserID INT; --推广员帐号DECLARE @ProxyID INT; --代理帐号DECLARE @Score INT=1000; --分数SELECT @UserID = [SpreaderID]FROM [QPAccountsDB].[dbo].[AccountsInfo]WHERE UserID = 5055;SELECT --查出推广员的代理帐号 @Pro
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;

 

本文由皇牌天下投注网发布于计算机教程,转载请注明出处:游标和递归sql 的一些代码

关键词:

上一篇:Shell编程

下一篇:没有了

最火资讯