mysql - 如果值在组中最高，则将列更新为 1，否则更新为 0

Question

我有一个包含以下列的表格:

player_id, datetime, player_level, is_latest

我需要做的是，当datetime对于玩家来说是最高的时，设置is_latest = 1。

这就是我检查玩家最高日期时间的方法:

SELECT player_id, MAX(datetime)
FROM players
GROUP BY player_id

现在我需要更新整个表:

UPDATE players
SET is_latest = /* 1 if datetime = MAX(datetime), 0 otherwise */

您对如何编写 UPDATE 语句有什么想法吗？如果我可以在单个 SQL 查询中使用 SELECT 和 UPDATE ，那就最好了。

该表有 3000 万行，因此我想要一个不涉及将所有行复制到其他地方的解决方案。

Answer 1

我不知道这是否是最快的方法，但这个查询应该做你想做的事情，并让你了解一般语法:

UPDATE players p
LEFT JOIN (
  SELECT player_id, MAX(datetime) max_date
  FROM players
  GROUP BY player_id
) t ON p.player_id = t.player_id AND p.datetime = t.max_date
SET p.is_latest = (t.max_date IS NOT NULL); 

使用左连接意味着与p.datetime = t.max_date谓词不匹配的行将为null，事实上，我们可以在 set 子句中使用 bool 表达式来确保只有最新的行将被设置为 1，而较旧的行将被设置为 0。

查看此SQL Fiddle举个例子。

一如既往，当涉及到测试更新等时，请确保有一个副本/备份...