SQL- 查询以获取最大日期低于特定日期的行

Question

抱歉，我无法在标题中更好地解释这一点。这基本上是我需要完成的:

Entity table: entity_id

BenchHistory table: entity_id, bench_id, bench_effective_date

Summary table: entity_id, effective_date

简而言之就是数据库布局。此查询查找每个实体的 effective_date来自 Summary table 。它还需要找到 bench_id对于那个特定的日期，通过查看 BenchHistory表并找到最大值 bench_effective_date小于 effective_date其中 BenchHistory.entity_id = Entity.entity_id .

这是我的:

SELECT
    Entity.entity_id
    Summary.effective_date
    BenchHistory.bench_id
FROM
    Entity
JOIN Summary ON Entity.entity_id = Summary.entity_id
JOIN BenchHistory ON Entity.entity_id = BenchHistory.entity_id

很简单，长凳部分是我遇到的麻烦。如何只选择一个 BenchHistory.bench_id , 那必须是最近的相对于 Summary.effective_date ？

为清楚起见，每个实体都有许多对应的 effective_dates和许多对应的bench_ids , 但只有一个 bench_id可以一次“有效”(最近一次)。我试图找到每一行的“有效”bench_id取决于该行的 effective_date .我需要通过确定哪个 bench_id 来做到这一点有一个 bench_effective_date小于 effective_date .

这是我的一个查询示例，也许这样会更容易可视化。

SELECT
    Entity.entity_id
    BenchHistory.bench_id
    Summary.effective_date
    BenchHistory.bench_effective_date
FROM
    Entity
JOIN Summary ON Entity.entity_id = Summary.entity_id
JOIN BenchHistory ON Entity.entity_id = BenchHistory.entity_id

这将给出如下输出:

entity_id    bench_id    effective_date    bench_effective_date
1            120         1/31/2011         6/30/2003
1            121         1/31/2011         3/22/2005
1            122         1/31/2011         11/03/2008
1            123         1/31/2011         1/21/2011
1            124         12/30/2010        5/15/2010
1            125         12/30/2010        10/06/2010

我要抓的，就是bench_id 123 为 1/31，因为它是 entity_id = 1 的最新替补席和 bench_id 125 为 12/30 等。这样一个结果集:

entity_id    bench_id    effective_date    bench_effective_date
1            123         1/31/2011         1/21/2011
1            125         12/30/2010        10/06/2010

谢谢，抱歉，如果这是一个简单的问题，但我已经研究了 6 个小时，尝试了各种子查询、聚合、GROUP BY 等等。我对 SQL 不是很熟悉。

:)

Answer 1

这不是一个简单的问题，我也花了很多时间。与 Mysql - Need to get latest of table A when referenced from table B 基本相同的问题并且与 mysql 功能请求相关 http://bugs.mysql.com/bug.php?id=2020 ，查看它以获取信息。

也许对你来说最简单的就是遵循这个例子:

假设您有一张表，其中包含每家商店中每件商品的价格。对于每件商品，您都希望看到最低价格和相关商店，您可以在其中以价格获得它!与您的示例完全相同 - 您想要一个最大修订版的记录。

create table prices ( goods varchar(10), price double, store varchar(10) );

insert into prices values ('car', 200, 'Amazon'), ('car', 150, 'CarStore'), ('Bread', 2, 'Baker1'), ('Bread', 1, 'Baker2');

select goods, min(price), 
    (select store 
     from prices as p 
     where p.goods = prices.goods
     order by price limit 1) as store
from prices
group by goods;