我在 SO 上看到了一些类似的问题,但是,我无法找到解决我的具体问题的方法.(仅供参考,这些不是我真正的专栏,只是一个简短的例子).
I've seen some similar types of questions on SO, however, I have not been able to find a solution to my specific issue. (FYI, these are not my real columns, just a shortened example).
我有一个基本表 my_table
:
user_1 | user_2 | 时间戳 | 注意(不是表格的一部分) |
---|---|---|---|
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 |
所以,我想做的是能够:
So, what I want to do is be able to:
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
但这不会返回我正在寻找的行.任何有关修复此查询的帮助都会很棒.
But this does not return the row I am looking for. Any help on fixing this query would be great.
非常感谢.
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
<小时>
顺便说一下,如果您想知道为什么您的原始查询不起作用,让我们分解一下:
By the way, in case you're curious why your original query didn't work, let's break down the pieces:
my_table
中选择一些东西...user_2
= 22timestamp
= (有些值,暂且搁置)my_table
...user_2
= 22timestamp
= (some value, let's put it aside for now)现在,回到 timestamp
值,它来自您的子查询:
Now, coming back to that timestamp
value, it comes from your subquery:
SELECT MAX( timestamp ) FROM my_table
请注意,此子查询不限制基于 user_2
的任何行——它询问整个表中的最大时间戳是多少.该最大时间戳是上表中的第一个:(user_1 = 23,user_2 = 25,timestamp = 2012-08-10 22:00:00).
Note that this subquery doesn't restrict any rows based on user_2
-- it asks for what's the max timestamp in the whole table. That max timestamp is the first one in your table above: (user_1 = 23, user_2 = 25, timestamp = 2012-08-10 22:00:00).
那么,让我们把它插回顶级查询:
So, let's plug that back to the top-level query:
my_table
中选择一些东西......你可以看到没有这样的一行.
... and you can see there isn't such a row.
这篇关于MySQL 按最新时间戳选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!