Mysql join 基于max(timestamp)

Question

我有一个网络应用程序，可以让客户为他们的服务定义每日费率。对于任何给定的一天，客户可以输入两个费率，一个小时费率 (rateTypeId=1) 和一个每日费率 (rateTypeId=2)。这些费率通常是预先指定的，而且经常会发生变化。我需要跟踪所有分配，但只提取最新的分配率。

我有两张 table 。第一个表简单地定义了我的费率结构，看起来像这样(简化):

表:RateDefinitions

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

我的第二个表跟踪分配给给定日期的费率。可以为给定日期分配多个费率，但我们将仅使用基于“entrytimestamp”的最新费率。

表:费率

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

现在我很难组合一个查询来提取给定时间范围内的所有最新利率分配。

我试过:

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

但那是行不通的。我在想我需要加入子选择语句，但不确定。我的结果每个日期应包含两行，类似于:

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

感谢您的任何建议。

Answer 1

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

对于 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