mysql - 如何按日期和型号年份过滤此 MySQL 查询？

Question

我有一张 table ，它看起来像这样:

id  year    effective   price
1   2000    2012-01-01  1000
2   2001    2012-01-01  1100
3   2002    2012-01-01  1200
4   2003    2012-01-01  1300
5   2000    2012-03-01  1500
6   2001    2012-03-01  1600
7   2002    2012-03-01  1700
8   2003    2012-03-01  1800
9   2000    2011-12-01  900
10  2001    2011-12-01  1000
11  2002    2011-12-01  1100
12  2003    2011-12-01  1200

在这个表中是一堆数据，其中包含许多项目的价格历史记录。它们没有任何规律的顺序，似乎人们随机进入数据库并进行了更改。我没有设置这个数据库，也没有开发插入和更新数据的应用程序。我的唯一目标是以客户希望的方式呈现指定的数据。

话虽这么说，但我很难获得所需的信息。在上面的示例数据中，我需要能够对价格进行排序并获得每个特定年份的不同数字。这必须按最终用户输入的最近生效日期进行过滤。

例如，如果用户想要最新的数据(截至今天)，响应应该是这样的:

5   2000    2012-03-01  1500
6   2001    2012-03-01  1600
7   2002    2012-03-01  1700
8   2003    2012-03-01  1800

另一方面，如果用户想要截至 2012 年 2 月 1 日的最新数据，那么它应该返回:

1   2000    2012-01-01  1000
2   2001    2012-01-01  1100
3   2002    2012-01-01  1200
4   2003    2012-01-01  1300

我的问题在于不知道如何构建此查询。甚至可以将其构建为单个查询，还是我必须执行多个查询才能获取数据？我希望占地面积尽可能小，因为我必须从共享年份但有其他不同数据的四个不同表中获取数据。

我试过这个查询没有效果:

select id, year, effective, price
from mytable
group by year
having effective < '2012-02-01'

结果如下:

1   2000    2012-01-01  1000
2   2001    2012-01-01  1100
3   2002    2012-01-01  1200
4   2003    2012-01-01  1300

我原以为这会给我正确的信息，因为这看起来合乎逻辑。

我试过这个查询，也没有效果:

select distinct year, id, effective, price
from mytable
group by effective
having effective < '2012-02-01'

返回这个结果:

1   2000    2012-01-01  1000
5   2000    2012-03-01  1500
9   2000    2011-12-01  900

这个查询似乎是完全错误的。

关于如何让它正确执行查询有什么建议吗？

Answer 1

如果您在子查询中过滤日期，则使用相关子查询获取每 年 的 MAX(effective) 并加入主表将起作用。这个想法是子查询将返回模型年份和生效日期的集合，它们是小于您在 WHERE 子句中指定的最大日期。可以将这些返回的列连接回主表以提取其余列。

SELECT 
  mytable.*
FROM
  mytable
  JOIN (
     /* Join on a subquery which returns the greatest date per year */
     SELECT MAX(effective) as effective, year
     FROM mytable 
     /* Filtering on some date in the subquery WHERE clause... */
     WHERE effective < '2012-01-01'
     GROUP BY year
  /* Since the subquery returns effective,year, join on both those cols against the main table */
  ) maxd ON mytable.effective = maxd.effective AND mytable.year = maxd.year

示例于 2012 年 1 月 1 日生效:http://sqlfiddle.com/#!2/05546/4

示例于 2013 年 1 月 1 日生效:http://sqlfiddle.com/#!2/05546/5