我有一个 4 列表,主键是 id、gameId 和 flag 的组合。
id | gameId | score | flag
--------------------------
1 | 1 | 10 | 1
1 | 1 | 20 | 0
2 | 1 | 1 | 0
1 | 2 | 10 | 1
3 | 2 | 1 | 0
我需要更新表格,以便:
(例如,通过将第 1 行和第 4 行的分数相加,第 1 行的分数为 20)
所以 SQL 完成后我的表应该是这样的:
id | gameId | score | flag
--------------------------
1 | 1 | 20 | 1
1 | 1 | 20 | 0
2 | 1 | 1 | 0
3 | 1 | 1 | 0
我怎么能用SQL写这个?
谢谢 :)
最佳答案
认为这会起作用:
尝试 1
UPDATE score_list AS t2
LEFT JOIN score_list AS t1 ON
(t1.id = t2.id AND t1.flag = t2.flag AND t1.gameId = 1)
SET
t1.score = t1.score + t2.score,
t2.gameId = IF(t1.gameId IS NULL, 1, t2.gameId)
WHERE t2.gameId = 2;
DELETE FROM score_list WHERE gameId = 2;
尝试 2
# add scores to gameId = 1
UPDATE score_list AS t2
LEFT JOIN score_list AS t1 ON
(t1.id = t2.id AND t1.flag = t2.flag AND t1.gameId = 1)
SET
t1.score = t1.score + t2.score
WHERE t2.gameId = 2;
# try to move gameID 2 to gameId 1, using ignore to allow rows to fail, as gameId alredy exists
UPDATE IGNORE score_list SET gameId = 1 WHERE gameId = 2;
# delete all not updated rows from last query
DELETE FROM score_list WHERE gameId = 2;
关于MySQL 使用同一个表中的值更新表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/11436814/