如何从 mysql 表中选择最新的一组日期记录
我将对各种 rpc 调用的响应存储在具有以下字段的 mysql 表中:
I am storing the response to various rpc calls in a mysql table with the following fields:
Table: rpc_responses
timestamp (date)
method (varchar)
id (varchar)
response (mediumtext)
PRIMARY KEY(timestamp,method,id)
为 method 和 id 的所有现有组合选择最新响应的最佳方法是什么?
What is the best method of selecting the most recent responses for all existing combinations of method and id?
对于每个日期，给定的方法/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.
有数十种方法、数千个 ID 和至少 365 个不同的日期
There are dozens of methods, thousands of ids and at least 365 different dates
示例数据:
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 "....."
想要的结果:
2009-01-10 getThud 16 "....."
2009-01-10 getBar 12 "....."
2009-01-11 getFoo 12 "....."
2009-01-11 getBar 16 "....."
(我不认为 this 是同一个问题- 它不会给我最新的 response)
(I don't think this is the same question - it won't give me the most recent response)
推荐答案
自我回答，但我不确定随着表格的增长，它是否会是一个足够有效的解决方案:
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);
相关文章