mysql - 使用 join 和 max 更新行

Question

我有以下两个表(简化模式):

score
-----
id
score

score_history
-------------
id
score_id (foreign key with score table)
score

我定期填充 score_history 表。我想为分数表中的每一行更新分数列，该分数列基于 score_history 中的分数以及关联的最大 ID 号。

一个例子可以是:

score entries
+----+-------+
| id | score |
+----+-------+
| 1  | 0     |
| 2  | 0     |
+----+-------+

score_history entries
+----+----------+-------+
| id | score_id | score |
+----+----------+-------+
| 1  | 1        | 15    |
| 2  | 2        | 10    |
| 3  | 1        | 14    |
| 4  | 2        | 11    |
+----+----------+-------+

在条目 3/4 存在于 score_history 之前，我希望在一个请求中将分数表中的分数更新为以下内容:

+----+-------+
| id | score |
+----+-------+
| 1  | 15    |
| 2  | 10    |
+----+-------+

在 score_history 中插入条目 3/4 之后，我再次希望相同的请求具有我的分数表:

+----+-------+
| id | score |
+----+-------+
| 1  | 14    |
| 2  | 11    |
+----+-------+

我尝试了多种方法(例如 https://stackoverflow.com/a/9396897/916630 )但未能成功。

有什么想法吗？

Answer 1

如果您正在寻找更新命令，它可能是

update 
score s 
join score_history sh on sh.score_id = s.id 
join ( 
  select max(id) as max_id, score_id  from score_history group by score_id
)x on x.max_id = sh.id and x.score_id = sh.score_id 
set s.score = sh.score ;