时间戳

首页 > 解决方案 > 如何更正在 sql server 中编写我的更新查询

sql - 如何更正在 sql server 中编写我的更新查询

问题描述

我如何编写我的 sql server 查询来更新正确的数量。这是我目前所拥有的，但这不是正确的解决方案。

我正在尝试通过 PlayerId 和 PlayerBonusId 计算 REdeemedBonusAmount 的总和，但我需要使用 DBT.ExternalWAllet 标志从数据库中获取正确的金额

UPDATE R SET          
        [RedeemedBonusAmount] = AGGR.[RedeemedBonusAmount]
    FROM #Results R
    JOIN 
    (
        SELECT  Aggr.[PlayerId],
                aggr.[PlayerBonusId],
                    CASE 
                    WHEN DBT.ExternalWallet=1 THEN SUM(Aggr.Amount1)
                    ELSE SUM(Aggr.[Amount2]) 
                END AS [RedeemedBonusAmount]
        FROM table_1 Aggr
        JOIN table_2 DPB ON Aggr.[PlayerId] = DPB.[PlayerId]
        JOIN table_3 DB ON DPB.BonusId = DB.Id
        JOIN table_4 DBT ON DB.BonusTypeId= DBT.Id
        WHERE  Aggr.[TimeId] BETWEEN 2018110100 AND 2018122123 and aggr.playerid=362920
        GROUP BY Aggr.[PlayerId],aggr.[PlayerBonusId],DBT.ExternalWallet
    ) AS AGGR ON R.playerid=Aggr.playerid and R.PlayerBonusId=Aggr.[PlayerBonusId]

如果我只运行选择，这是我得到的表格：

PlayerId    PlayerBonusId   RedeemedBonusAmount
362920      619622          380.000000
362920      619624          19.000000
362920      619622          0.000000
362920      619624          0.000000

标签： sqlsql-server

解决方案

不是对 SUM 进行 CASE，而是对 CASE 求和。

那么你也不需要在 DBT.ExternalWallet 上进行 GROUP，外部查询的连接中没有用到。

UPDATE R 
SET [RedeemedBonusAmount] = AGGR.[RedeemedBonusAmount]
FROM #Results R
JOIN 
(
  SELECT  Aggr.[PlayerId],
          Aggr.[PlayerBonusId],
          SUM(CASE 
              WHEN DBT.ExternalWallet=1 THEN Aggr.Amount1
              ELSE Aggr.[Amount2]
              END) AS [RedeemedBonusAmount]
   FROM table_1 Aggr
   JOIN table_2 DPB ON Aggr.[PlayerId] = DPB.[PlayerId]
   JOIN table_3 DB ON DPB.BonusId = DB.Id
   JOIN table_4 DBT ON DB.BonusTypeId= DBT.Id
   WHERE  Aggr.[TimeId] BETWEEN 2018110100 AND 2018122123 and aggr.playerid=362920
   GROUP BY Aggr.[PlayerId],aggr.[PlayerBonusId]
) AS AGGR ON R.playerid=Aggr.playerid and R.PlayerBonusId=Aggr.[PlayerBonusId]

推荐阅读