我有一个mysql表，用于记录两人比赛，如下所示：
- gameid
- id1 // id of player 1
- id2 // id of player 2
- score1 // score of player 1
- score2 // score of player 2
- state // state of the games is initially 0, then the score updates are made and in order to prevent further updates the state must be updated to 1
我需要检查记录，并根据分数更新另一个表"用户"。例如：如果score1>score2我需要更新3件事：
1- the state of the game // from 0 to 1
2- in table "users" add 1 point to the column score for the user with userid = id1
2- in table "users" subtract 1 point from the column score for the user with userid = id2
到目前为止，我可以更新1和2，但我需要一个命令中的所有3个更新：
UPDATE dbo.games AS GA , dbo.users AS US
SET GA.state = 1, US.score = US.score + 1
WHERE US.id = GA.id1 AND GA.state = 0 and GA.score1 > GA.score2
我可以分离+1和-1命令，它会很好地工作。但是当命令运行时，两个用户的分数都应该更新。有人能帮忙吗？
这应该做到：
update dbo.games as ga, dbo.users as us
set
ga.state = 1,
us1.score = us1.score + case
when
(ga.score1 > ga.score2 and us.id = ga1.id1)
or (ga.score2 > ga.score1 and us.id = ga2.id2)
then 1
else -1
end
where
ga.state = 0
and ga.score1 <> ga.score2
and us.id in (ga.id1, ga.id2)
逻辑是在用户表中选择两行，然后执行条件逻辑来决定是添加还是删除点。
注意：您没有告诉您希望如何处理平局竞争，因此此查询明确忽略它们。
UPDATE dbo.games AS GA , dbo.users AS US
SET GA.state = 1, (CASE WHEN US.id =GA.id1 THEN US.score = US.score + 1
ELSE WHEN US.id=id2 THEN US.score =US.score-1 END)
WHERE GA.state = 0 and GA.score1 > GA.score2
当US.id=id1时，此查询将分数增加1，当US.id=id2 时，该查询将分数减少1