MySQL 5.7.25 group by sub-query and order by "nonaggregated column"错误

Question

简而言之，我尝试按日期对日期集进行排序，然后按另一列分组，从而选择每列的最新行。

查询:

SELECT name, datetime
FROM (
    SELECT *
    FROM `requests`
    ORDER BY datetime
) a
GROUP BY a.name;

错误:

#1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a.datetime' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

示例表:

CREATE TABLE `requests` (
 `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(10) DEFAULT NULL,
 `datetime` datetime DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

目标是防止此错误发生而无需更改默认的 sql-mode。

在阅读了更多关于 group by 和 only_full_group_by 之后，我目前不明白为什么子查询会影响外部查询。

查询是按照https://stackoverflow.com/a/16307932/3852461写的

Answer 1

如果没有像 sum() 或 min() 这样的聚合函数，您不应该使用 GROUP BY。

如果您想要不同的结果，请使用 DISTINCT

SELECT distinct name, datetime
FROM (
    SELECT *
    FROM `requests`
    ORDER BY datetime
) a

但是如果你需要单行的名字，你应该为日期时间使用聚合函数，例如

SELECT  name, max(datetime)
FROM (
    SELECT *
    FROM `requests`
    ORDER BY datetime
) a
group by name