mysql - 使用 groupBy 为最大查询创建最大限制

Question

我有这种 table

+----+------------+-------+------------+
| id | company_id | price |  periods   |
+----+------------+-------+------------+
|  1 | A1         |   500 | 2016-07-12 |
|  2 | A2         |   540 | 2018-01-21 |
|  3 | A1         |   440 | 2017-01-19 |
|  4 | A2         |   330 | 2016-01-12 |
|  5 | A3         |   333 | 2018-01-22 |
+----+------------+-------+------------+

一开始我只想选择最大值 periods并按 company_id 分组通过使用这个查询

SELECT salesreport.* FROM salesreport
 INNER JOIN (SELECT company_id, MAX(periods) AS max_periods FROM salesreport WHERE periods < 2016-01-01 GROUP BY company_id) AS latest_report
       ON salesreport.company_id = latest_report.company_id AND salesreport.periods = latest_report.max_periods;

它会像这样返回表格

+----+------------+-------+------------+
| id | company_id | price |  periods   |
+----+------------+-------+------------+
|  2 | A2         |   540 | 2018-01-21 |
|  3 | A1         |   440 | 2017-01-19 |
|  5 | A3         |   333 | 2018-01-22 |
+----+------------+-------+------------+

但现在我还想限制我想要与分组相处的最长期限所以，假设我想要 periods <= 2017-01-01那么我期望的是

+----+------------+-------+------------+
| id | company_id | price |  periods   |
+----+------------+-------+------------+
|  1 | A1         |   500 | 2016-07-12 |
|  4 | A2         |   330 | 2016-01-12 |
+----+------------+-------+------------+

我尝试使用 having还有正常的 where但它们都不起作用

SELECT salesreport.* FROM salesreport
 INNER JOIN (SELECT company_id, MAX(periods) AS max_periods FROM salesreport GROUP BY company_id HAVING MAX(periods) < 2017-01-01) AS latest_report
       ON salesreport.company_id = latest_report.company_id AND salesreport.periods = latest_report.max_periods;



SELECT salesreport.* FROM salesreport
 INNER JOIN (SELECT company_id, MAX(periods) AS max_periods FROM salesreport WHERE periods < 2017-01-01 GROUP BY company_id ) AS latest_report
       ON salesreport.company_id = latest_report.company_id AND salesreport.periods = latest_report.max_periods;

然后我意识到可能是因为 MAX(periods)但是，如果不使用此聚合，我如何获得 groupBy 中的最高值？所以也许查询专家可以提供洞察力来理解和解决这个问题....

更新

感谢@Accountant，他创建了这个来帮助我们查看和找到解决方案，请随时在这里进行试验 http://sqlfiddle.com/#!9/a7ae29/31

Answer 1

您只是错过了在日期值上添加单引号。这是基于 sqlfiddle 演示的工作，用户 ID:accountant

正确:MAX(periods) < '2017-01-01'

旧:MAX(periods) < 2017-01-01