MySQL 如何从mysql表中选择最新的一组日期记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/435703/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select the most recent set of dated records from a mysql table
提问by Ken
I am storing the response to various rpc calls in a mysql table with the following fields:
我将对各种 rpc 调用的响应存储在具有以下字段的 mysql 表中：
Table: rpc_responses
timestamp (date)
method (varchar)
id (varchar)
response (mediumtext)
PRIMARY KEY(timestamp,method,id)
What is the best method of selecting the most recent responses for all existing combinations of methodand id?
为method和 的所有现有组合选择最新响应的最佳方法是什么id？
For each date there can only be one response for a given method/id.
Not all call combinations are necessarily present for a given date.
There are dozens of methods, thousands of ids and at least 365 different dates
对于每个日期，给定的方法/ID 只能有一个响应。
对于给定日期，并非所有调用组合都必须存在。
有数十种方法、数千个 ID 和至少 365 个不同的日期
Sample data:
样本数据：
timestamp method id response
2009-01-10 getThud 16 "....."
2009-01-10 getFoo 12 "....."
2009-01-10 getBar 12 "....."
2009-01-11 getFoo 12 "....."
2009-01-11 getBar 16 "....."
Desired result:
想要的结果：
2009-01-10 getThud 16 "....."
2009-01-10 getBar 12 "....."
2009-01-11 getFoo 12 "....."
2009-01-11 getBar 16 "....."
(I don't think thisis the same question - it won't give me the most recent response)
（我不认为这是同一个问题 - 它不会给我最新的response）
采纳答案by Ken
Self answered, but I'm not sure that it will be an efficient enough solution as the table grows:
自我回答，但我不确定随着表格的增长，这将是一个足够有效的解决方案：
SELECT timestamp,method,id,response FROM rpc_responses
INNER JOIN
(SELECT max(timestamp),method,id FROM rpc_responses GROUP BY method,id) latest
USING (timestamp,method,id);
回答by velcrow
Use this solution with caution:
it is not guaranteed to work in future versions of mysql
it is not known to work in mariadb 5.5
谨慎使用此解决方案：
不能保证在未来版本的 mysql 中工作
它不知道在 mariadb 5.5 中工作
This can query may perform well, because there are no joins.
这可以查询可能执行得很好，因为没有连接。
SELECT * FROM (
SELECT timestamp, method, id, response
FROM rpc_responses
WHERE 1 # some where clause here
ORDER BY timestamp DESC
) as t1
GROUP BY method
The "group by", collapses the result set on method, and returns only 1 row per method, the most recent one, because of the ORDER BY timestamp DESC in the inner query.
由于内部查询中的 ORDER BY 时间戳 DESC，“group by”折叠方法上的结果集，并且每个方法仅返回 1 行，即最近的一行。
FYI, PostgreSQL has a way of doing this built into the language:
仅供参考，PostgreSQL 有一种内置于语言中的方法：
SELECT DISTINCT ON (method) timestamp, method, id, response
FROM rpc_responses
WHERE 1 # some where clause here
ORDER BY method, timestamp DESC
回答by versek
Try this...
尝试这个...
SELECT o1.id, o1.timestamp, o1.method, o1.response
FROM rpc_responses o1
WHERE o1.timestamp = ( SELECT max(o2.timestamp)
FROM rpc_responses o2
WHERE o1.id = o2.id )
ORDER BY o1.timestamp, o1.method, o1.response
...it even works in Access!
...它甚至可以在 Access 中使用！
回答by Simon
Subquery is very taxing when the data set becomes larger.
当数据集变大时，子查询非常繁重。
Try this:
尝试这个：
SELECT t1.*
FROM rpc_responses AS t1
INNER JOIN rpc_responses AS t2
GROUP BY t1.method, t1.id, t1.timestamp
HAVING t1.timestamp=MAX(t2.timestamp)
ORDER BY t1.timestamp, t1.method, t1.response;
回答by charles
i used this,worked for me
我用过这个，对我有用
select max(timestamp),method,id from tables where 1 group by method,id order by timestamp desc
回答by Neil
The concept of "most recent" is fairly vague. If you mean something like the 100 most recent rows then you can just add a TOP(100)to your SELECTclause.
“最近”的概念相当模糊。如果您的意思是最近的 100 行，那么您只需TOP(100)在SELECT子句中添加 a即可。
If you mean the "most recent" based on a the most recent date then you can just do
如果您的意思是基于最近日期的“最近”，那么您可以这样做
SELECT timestamp,method,id,response
FROM rpc_responses
HAVING max(timestamp) = timestamp
回答by spi
...is more than one year later but i might help someone To select all the queries starting from latest
...一年多之后，但我可能会帮助某人选择从最新开始的所有查询
SELECT *
FROM rpc_responses
ORDER BY timestamp DESC
