绕过only_full_group_by限制

Question

我有一个简单的表：

mysql> select deviceId, eventName, loggedAt from foo;
+----------+------------+---------------------+
| deviceId | eventName  | loggedAt            |
+----------+------------+---------------------+
|        1 | foo        | 2020-09-18 21:27:21 |
|        1 | bar        | 2020-09-18 21:27:26 |
|        1 | last event | 2020-09-18 21:27:43 |  <--
|        2 | xyz        | 2020-09-18 21:27:37 |
|        2 | last event | 2020-09-18 21:27:55 |  <--
|        3 | last one   | 2020-09-18 21:28:04 |  <--
+----------+------------+---------------------+

并且我想要选择具有最新CCD_ 2的每个CCD_。为了清楚起见，我在上表中用箭头标记了这些行。

如果我在上面的查询中附加group by id，我会得到臭名昭著的：

SELECT列表的表达式#2不在GROUP BY子句中，并且包含非聚合列"foo.eventName"，该列在功能上不依赖于GROUP BY子句的列；这与sql_mode=only_full_group_by 不兼容

并且我不想更改sql_mode。

我已经非常接近我想要使用的：

select deviceId, any_value(eventName), max(loggedAt) from foo group by deviceId;

但显然CCD_ 5返回一个随机结果。

我该如何解决这个问题？

Answer 1

ONLY_FULL_GROUP_BY是一件好事：它强制执行基本的SQL标准规则，MySQL对此一直很松懈。即使您禁用了它，您也会得到与any_value()相同的结果。

您有一个前1-位组问题，无法找到每台设备最近日期的整行。聚合不是正确的工具，您需要的是过滤数据集。

一个选项使用相关的子查询：

select f.*
from foo f
where f.loggedat = (
select max(f1.loggedate) from foo where f1.deviceid = f.deviceid
)

在MySQL 8.0中，您还可以使用row_number():

select *
from (
select f.*, row_number() over(partition by deviceid order by loggedat desc) rn
from foo f
) f
where rn = 1