MySQL：如何仅选择每个用户的最后更新？

Question

我有一个名为'user_log'的表，它有以下字段：

• id
• user_id
• status_text
• timestamp

如何编写查询以仅选择该表中所有用户的最新更新？

感谢您的帮助 :)

- KeyStroke

---

你只需要用户ID和日期，还是也需要其他相关行的信息？ - OMG Ponies

用户ID和时间戳上是否有唯一约束？ - Mark Byers

Answer 1

假设ID是自增ID，你可以这样做：

SELECT * 
FROM user_log 
INNER JOIN 
(SELECT MAX(id) as id FROM user_log GROUP BY user_id) last_updates 
ON last_updates.id = user_log.id

如果你想要非常严谨并处理所有边角情况（相同时间戳的多次更新，乱序插入等），你可以使用时间戳：

SELECT * 
FROM user_log
INNER JOIN
(SELECT MAX(id)
 FROM user_log 
 INNER JOIN 
 (SELECT DISTINCT user_id, MAX(timestamp) as timestamp 
  FROM user_log GROUP BY user_id
 ) last_updates 
 ON last_updates.user_id = user_log.user_id 
    AND last_updates.timestamp = user_log.timestamp
) last_update
ON last_update.id = user_log.id

- Eric Petroelje

---

你的第二个查询出现了这个错误：在'on clause'中未知的列'last_udpates.user_id'。 - Mark Byers

@Mark - 抱歉，是个打字错误。'last_udpates' 应该是 'last_updates'。现在已经修复了。 - Eric Petroelje

Answer 2

使用：

  SELECT ul.user_id,
         MAX(ul.timestamp)
    FROM USER_LOG ul
GROUP BY ul.user_id

- OMG Ponies

Answer 3

相关子查询可用于获取具有最新时间戳的记录的ID。即使时间戳不唯一或ID未按顺序给出，此方法也应该有效。

select
    ul.id,
    ul.user_id,
    ul.status_text, 
    ul.timestamp
from
    user_log ul
where 
    ul.id = (select top 1 ul2.id 
            from user_log ul2 
            where ul2.user_id = ul.user_id
            order by ul2.timestamp desc)

- Jeffrey L Whitledge

Answer 4

如果您在（user_id，timestamp）上没有唯一约束，但仍希望保证每个用户只返回一行，则可以使用此查询：

SELECT id, user_id, status_text, timestamp
FROM (
    SELECT *,  @prev <> user_id AS is_newest, @prev := user_id
    FROM user_log, (SELECT @prev := -1) AS vars
    ORDER BY user_id, timestamp DESC, id DESC
) AS T1
WHERE is_newest

结果：

1, 1, 'Foo', '2010-01-01 00:00'
4, 2, 'Bar', '2010-01-01 01:00'

测试数据：

CREATE TABLE user_log (id INT NOT NULL, user_id INT NOT NULL, status_text NVARCHAR(100) NOT NULL, timestamp NVARCHAR(100) NOT NULL);
INSERT INTO user_log (id, user_id, status_text, timestamp) VALUES
(1, 1, 'Foo', '2010-01-01 00:00'),
(2, 1, 'Bar', '2010-01-01 00:00'),
(3, 2, 'Foo', '2010-01-01 00:00'),
(4, 2, 'Bar', '2010-01-01 01:00');

- Mark Byers

Answer 5

尝试使用：

SELECT user_id, MAX(timestamp) FROM user_log GROUP BY user_id

- systempuntoout

---

这将获取每个用户的最新时间戳，但不能保证返回最新的状态文本，这是OP想要的。 - michael

Answer 6

这是 n-n 关系吗？如果不是，根据标准指南，状态文本和时间戳应被最大的表格吸收。

如果不是，请尝试

SELECT id,MAX(timestamp) FROM user_log GROUP BY user_id;

- Ben

---

这将获取每个用户的最新时间戳，但不能保证返回最新的状态文本，这正是OP想要的。 - michael

@michael从未表示他想获取status_text，只是想要每个用户的最新记录...此外，他可以自己添加所需的字段，这不是一个复制/粘贴解决方案网站，你不能为你的项目得到黑白答案。 - Ben