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

问题描述

 表：rpc_responses $ b我正在将各种rpc调用的响应存储在mysql表中，具有以下字段： 
 $ b timestamp（date）
方法（varchar）
 id（varchar）
响应（中文）
 
 PRIMARY KEY（timestamp， ）
  

为$ $ c $的所有现有组合选择最新回复的最佳方法是什么？ c>方法和 id ？

• 对于每个日期，对于给定的方法/ ID，只能有一个响应。




• 并非所有呼叫组合在给定日期都必须存在。 >




• 有数十种方法，数千种ids和至少365个不同的日期

样本数据：

  timestamp方法id响应
 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.....
  

（我不认为这个是同样的问题 - 它不会给我最新的响应）

解决方案

自我回答，但我不确定它将是一个有效的解决方案，因为表增长：

  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）; 
  

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)

What is the best method of selecting the most recent responses for all existing combinations of method and 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

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 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屋！