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;