如果投注获胜，则更新余额

Question

我正在开发一个小型博彩网站，我有tblAccounts和tblBackers。
我希望在tblAccounts用户投注获胜者时更新用户余额列。

投注存储在tblBackers中，该具有列amountBacked，backersAccountID以及他们选择赢得的用户/玩家(playerBacked (

获胜的帐户 ID 作为@accountID传入，所以我需要它来检查tblBackers和
使用每次出现playerBacked = @accountID的金额更新backersPlayerID余额

这是我到目前为止为此创建的过程

CREATE PROCEDURE [dbo].[UpdatePayout]
@accountID uniqueidentifier
AS
BEGIN
SET NOCOUNT ON;
IF (SELECT playerBacked from tblBackers)=@accountID
BEGIN
UPDATE tblAccounts
SET balance = balance + (SELECT amountBacked FROM tblBackers WHERE 
playerBacked = @accountID)
WHERE AccountID=(SELECT backersAccountID from tblBackers)
END
END

这是遇到的错误

子查询返回了 1 个以上的值。
当子查询跟在 =、！=、<= 、>、>=
之后时，不允许这样做 或者当子查询用作表达式时。

Answer 1

您只需要从内部查询返回一个结果， 总和聚合会将账户 ID 支持的所有金额实例相加，然后返回该值

CREATE PROCEDURE [dbo].[UpdatePayout]
@accountID uniqueidentifier
AS
BEGIN
SET NOCOUNT ON;

IF (SELECT playerBacked from tblBackers)=@accountID
BEGIN
UPDATE tblAccounts
SET balance = balance + (SELECT SUM(amountBacked) FROM tblBackers WHERE 
playerBacked = @accountID)
WHERE AccountID=(SELECT backersAccountID from tblBackers)
END
END

Answer 2

如果我理解正确的话 tblBackers有投注其他玩家的用户列表以及他下注的金额。 tblAccounts有用户列表和他的余额。

因此，例如，当一个AccountID在99999上下注12345时，他必须记入他下注的25个金额

CREATE TABLE #tblBackers (
amountBacked MONEY, 
backersAccountID INT,
playerBacked INT)
CREATE TABLE #tblAccounts (
Balance MONEY, 
AccountID INT)

INSERT INTO #tblBackers VALUES (25,12345,99999)
INSERT INTO #tblBackers VALUES (25,98765,88888)
INSERT INTO #tblBackers VALUES (25,76543,77777)
INSERT INTO #tblAccounts VALUES (50,12345)
GO
CREATE PROCEDURE [dbo].[UpdatePayout]
@AccountID INT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT 1 from #tblBackers WHERE backersAccountID = @AccountID)
BEGIN
    UPDATE #tblAccounts
    SET balance = balance + (SELECT SUM(amountBacked) FROM #tblBackers WHERE 
    backersAccountID = @AccountID)
    WHERE AccountID IN (SELECT backersAccountID from #tblBackers WHERE backersAccountID = @AccountID)
END
END

EXEC [UpdatePayout] 12345

SELECT * FROM #tblAccounts

更新了 SQL 查询

CREATE TABLE #tblBackers (
amountBacked MONEY, 
backersAccountID INT,
playerBacked INT)
GO
CREATE TABLE #tblAccounts (
Balance MONEY, 
AccountID INT)
GO
INSERT INTO #tblBackers VALUES (25,12345,99999)
INSERT INTO #tblBackers VALUES (25,12345,66666)
INSERT INTO #tblBackers VALUES (25,98765,88888)
INSERT INTO #tblBackers VALUES (25,76543,77777)
GO
INSERT INTO #tblAccounts VALUES (50,12345)
GO
SELECT * FROM #tblAccounts
GO
CREATE PROCEDURE [dbo].[UpdatePayout]
@AccountID INT
AS
BEGIN
SET NOCOUNT ON;
    IF EXISTS (SELECT 1 from #tblBackers WHERE playerBacked = @AccountID)
    BEGIN
        UPDATE #tblAccounts
        SET balance = Balance + (SELECT SUM(amountBacked)*2 FROM #tblBackers WHERE 
        playerBacked = @AccountID) 
        WHERE AccountID IN (SELECT backersAccountID from #tblBackers WHERE playerBacked = @AccountID)
    END
END
GO
EXEC [UpdatePayout] 99999
GO
SELECT * FROM #tblAccounts

更新了 SP 以处理多条记录

ALTER PROCEDURE [dbo].[UpdatePayout]
@AccountID INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Test TABLE
(
RowID INT IDENTITY(1,1),
AmountBacked MONEY,
BackersAccountID INT,
PlayerBacked INT
)
INSERT INTO @Test 
SELECT AmountBacked, BackersAccountID, PlayerBacked FROM #tblBackers WHERE PlayerBacked = @AccountID
DECLARE @AmountBacked MONEY
DECLARE @BackersAccountID INT
    WHILE EXISTS (SELECT 1 from @Test WHERE PlayerBacked = @AccountID)
    BEGIN
        SELECT @AmountBacked = AmountBacked, @BackersAccountID = BackersAccountID FROM @Test WHERE PlayerBacked = @AccountID
        UPDATE #tblAccounts
        SET balance = Balance + (SELECT SUM(@AmountBacked)*2) 
        WHERE AccountID IN (@BackersAccountID)
        DELETE FROM @Test WHERE BackersAccountID = @BackersAccountID
        SELECT @BackersAccountID = NULL
        SET @AmountBacked = NULL
    END
END
GO
EXEC [UpdatePayout] 99999
GO