我有一个 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

我需要更新表格,以便:
  • 将所有 gameId 2 分数添加到 gameId 1 中,其中 id 和标志相同。
    (例如,通过将第 1 行和第 4 行的分数相加,第 1 行的分数为 20)
  • 如果发生上述情况,则需要删除 gameId 2 行。
  • 如果没有找到上面的(例如,有一行 gameId 为 2 但 id 和 flag 与另一行不匹配),则可以将 gameId 更改为 1。

  • 所以 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/

    10-13 01:02