MySQL基于最大(时间戳)加入 [英] Mysql join based on max(timestamp)

问题描述

我有一个网络应用程序，可让客户定义其服务的每日费率.对于任何给定的一天，客户端可以输入两个费率，一个小时费率(rateTypeId = 1)和一个DailyRate(rateTypeId = 2).这些费率通常是预先分配的，并且经常更改.我需要跟踪所有分配，但只能提取最新分配的费率.

I have a web app that lets a client define a daily rate for their service. For any given day, the client can enter two rates, an hourly rate (rateTypeId=1) and a dailyRate (rateTypeId=2). These rates are often assigned in advance, and they often change. I need to track all assignments, but only pull the latest assigned rate.

我有两个桌子.第一张表只是定义了我的汇率结构，看起来像这样(简化):

I have two tables. The first table simply defines my rate structure and looks like this (simplified):

表:RateDefinitions

Table: RateDefinitions

RATECODE ----- RATE
31 ---------------- 5.00
32 ---------------- 6.00
33 ---------------- 7.00

我的第二张表跟踪分配给给定日期的汇率.给定日期可以指定多个汇率，但是我们只会根据"entrytimestamp"使用最新汇率.

My second table tracks the rates assigned to given dates. More than one rate can be assigned to a given date, but we will only used the latest rate based on the 'entrytimestamp'.

表:费率

ID --- RATETYPEID --- RATECODE ------ DATE -------- ENTRYTIMESTAMP
1 ---------- 1 --------------- 31 ---------- 20091010 ---------- 1100000000
2 ---------- 2 --------------- 33 ---------- 20091010 ---------- 1100000000
3 ---------- 1 --------------- 32 ---------- 20091010 ---------- 1200000000

现在，我很难对一个查询进行整理，该查询将提取给定时间范围内的所有最新费率分配.

Now I'm having difficulty putting together a query that will pull all the latest rate assignments for a given timeframe.

我尝试过:

select r.id, r.rateTypeId, r.rateCode, max(r.entryTimestamp), rd.rate
from rates r
join rateDefinitions rd
on r.rateCode=rd.rateCode
where date=20091010
group by startDate, rateTypeId

但是那不会做.我在想我需要加入一个subselect语句，但不确定.我的结果每个日期应包含两行，类似于:

But that's not going to do it. I'm thinking I need a join on a subselect statement, but not sure. My results should contain two rows per date, similar to:

ID --- RATETYPEID --- RATECODE ---------- ENTRYTIMESTAMP ----- RATE
3 ----------- 1 --------------- 32 -------------------- 1200000000 ----------6.00
2 ----------- 2 --------------- 33 -------------------- 1100000000 ----------7.00

谢谢您的建议.

推荐答案

此问题相当普遍.您需要一个子查询来抛出最大时间戳和ratetypeid(这是分组的基础)，然后从该子查询行的内部联接中选择所有其他内容.

This problem is fairly common. You need a subquery to throw out the max timestamp and the ratetypeid (which is the basis for grouping) and then select everything else from an inner join of this subquery rows and everything else.

对于MySQL:

SELECT ratecode, rate, id, ratetypeid, date, entrytimestamp 

FROM ratedefinitions, 
(SELECT ratetypeid, MAX(entrytimestamp) AS max_timestamp FROM Rates 
GROUP BY ratetypeid) AS inner_table

WHERE

inner_table.ratetypeid = ratetypeid
AND innertable.max_timestamp = timestamp

这篇关于MySQL基于最大(时间戳)加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！