MySQL 5.7 | MySQL 5.7分组依据 |独特|聚合错误

Question

我使用的是 MySQL 5.7。

我有一个表格如下:

--------------------------------------------------
| id | currentcy_id | rate |      created_at     |
--------------------|------|---------------------|
|  1 |      1       |   1  | 2017-11-07 23:19:48 |
|  2 |      2       |   2  | 2017-11-07 23:20:48 |
|  3 |      3       |   4  | 2017-11-07 23:21:48 |
|  4 |      1       |   2  | 2017-11-07 23:22:48 |
--------------------------------------------------

我尝试通过执行以下操作来获取每个不同 currency_id 的最新值:

SELECT `currentcy_id`, `rate`, MAX(`created_at`)  
FROM `currency_reates` 
GROUP BY (`currentcy_id`)

我也尝试了 DISTICT 函数:

SELECT DISTINCT(`currentcy_id`), `rate`, MAX(`created_at`)  
FROM `currency_reates`

我从两个有关聚合的查询中收到错误。

注意:我在 MySQL 中禁用了 STRICT 选项并且它可以工作，但我不想这样做，我想要正确的方法(新的一)。

Answer 1

SELECT C.`currentcy_id`, C.`rate`, C.`created_at`
FROM `currency_reates` C
JOIN ( SELECT `currentcy_id`,  MAX(`created_at`)  as m_date
       FROM `currency_reates` 
       GROUP BY (`currentcy_id`)
     ) as T
  ON C.`currentcy_id` = T.`currentcy_id`
 AND C.`created_at` = T.m_date