基于最新的时间戳选择MySQL记录

Question

我在SO上看到了一些类似的问题，但是我没有找到解决我的特定问题的方法。(FYI，这些不是我的真实列，只是一个缩短的例子)。

我有一个基本表my_table：

user_1	user_2	timestamp	note(not part of table)
23	25	2012-08-10 22:00:00
24	22	2012-08-10 19:00:00	<=== 我想返回此行
24	22	2012-08-10 17:00:00
21	17	2012-08-10 15:00:00

所以，我想做的是能够：

 1) Select the "newest" row, based on timestamp AND 
 2) Select the 'user_2' column when given a value.  

我尝试了类似这样的代码：

 SELECT *
 FROM my_table
 WHERE user_2 = 22
 AND timestamp = (
 SELECT MAX( timestamp )
 FROM my_table )
 LIMIT 1 

但是这个查询没有返回我要找的行。对于修复这个查询的任何帮助都将不胜感激。

非常感谢。

- Dodinas

Answer 1

SELECT * FROM my_table -- standard stuff
   WHERE user_2 = 22 -- predicate
   ORDER BY timestamp DESC -- this means highest number (most recent) first
   LIMIT 1; -- just want the first row

---

编辑：

顺便说一下，如果你好奇为什么你原来的查询不起作用，让我们拆分一下这些部分：

• 从my_table中选择一些东西...
• 其中user_2=22
• 而timestamp=(某个值，暂时放一边)
• 限制1条结果

现在，回到那个timestamp的值，它来自于你的子查询：

SELECT MAX( timestamp ) FROM my_table

请注意，该子查询不会基于user_2限制任何行 - 它要求在整个表中找到最大的时间戳。该最大时间戳是上面你的表格中的第一个时间戳：（user_1 = 23，user_2 = 25，timestamp = 2012-08-10 22:00:00）。

所以，让我们将其插回到顶级查询中：

- 从my_table选择一些内容... - where user_2 = 22 - and timestamp = 2012-08-10 22:00:00 - limit 1

...然后你可以看到没有这样的行。

- yshavit

---

非常感谢。我让自己太难了。这就是我在过去36个小时保持清醒的后果。 :-) - Dodinas

@Dodinas 不用谢！我发现从非SQL编程背景来看，我们一遍又一遍地被教导将问题分解成更小的问题，这种倾向也会出现在SQL查询中。不幸的是，这通常会使它们变得更加复杂--而且效率也更低。这需要一些时间来适应。顺便说一下，我编辑了我的问题，以解释你最初的查询出了什么问题。 - yshavit

太好了！非常有帮助。再次感谢。 - Dodinas

哈哈哈，这太聪明了。简单易懂，点个赞。 - Plummer

我在想当有很多行数据（比如10万行以上）时，那个查询的性能会如何。引擎需要在返回列表的第一个项目之前对所有内容进行排序。如果我在时间戳列上创建索引，引擎是否足够智能，可以浏览B树以找到我要查找的一个元素，而不是执行整个排序过程？ - Philibert Perusse

是的，这是索引的主要用途之一。您可以使用“EXPLAIN SELECT…”进行验证。在这种情况下，您需要在（user_2，timestamp）上建立索引。从左到右，索引可以满足您想要的任意数量的相等性，然后排序。如果索引中没有user_2列，则引擎必须扫描时间戳索引，直到找到具有正确user_2的行。 - yshavit

Answer 2

另一种方法是在计算 MAX(timestamp) 时按user_2列分组。MAX（timestamp）不会计算整个表中的最新日期，而是计算具有相同user_2值的记录组中的最新时间戳每个组。因此，例如，您的查询可能是：

SELECT * FROM my_table
WHERE user_2 = 22
AND timestamp =
  (SELECT MAX(timestamp) FROM my_table
   WHERE user_2 = 22
   GROUP BY user_2)
LIMIT 1;

这个查询来自我在这个精彩回答中找到的答案。

- Kevin

---

这给被接受的答案带来了什么优势？ - The Coder

Answer 3

如果在SQL Server中遇到类似问题，以下内容可解决（之前帖子中建议的MySQL查询并不适用于SQL Server）：

SELECT * FROM my_table 
WHERE    timestamp =  ( SELECT MAX( timestamp ) FROM my_table 
                        WHERE user_2 = 22 )

- RicardoMarquez

---

我得出了相同的解决方案，但是即使在时间戳上建立索引，由于表格很大，查询仍然非常缓慢。有没有更聪明的方法？ - Miro Krsjak

Answer 4

这就是我得到的全部内容。

SELECT timestamp 
       FROM my_table 
       WHERE user_22 = '22' 
       ORDER BY timestamp DESC /*or ASC*/

当你查询它时，代码将会是这样的

while($row = mysql_fetch_array(the sql query)){
$timestamp = $row['timestamp']
}

- Bryan Bojorque

Answer 5

这是我找到的解决办法：

 SELECT *
 FROM my_table AS t1
 WHERE user_2 = 22
 AND timestamp = (
 SELECT MAX( timestamp )
 FROM my_table AS t2
 WHERE t1.user_2 = t2.user_2 );

如果你想查看每个唯一用户的最新时间戳，请简单地删除WHERE user_2 = 22。

- JaredCS