如何从 mysql 表中选择最新的一组日期记录 [英] How to select the most recent set of dated records from a mysql table

问题描述

我将对各种 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);

这篇关于如何从 mysql 表中选择最新的一组日期记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！