MySQL“分组依据”和“排序依据”

Question

我希望能够从电子邮件表中选择一堆行，并按发件人对它们进行分组。我的查询如下所示：

 SELECT
    `timestamp`, `fromEmail`, `subject`
FROM `incomingEmails`
GROUP BY LOWER(`fromEmail`)
ORDER BY `timestamp` DESC

该查询几乎可以按我的意愿工作——它选择按电子邮件分组的记录。问题是主题和时间戳与特定电子邮件地址的最新记录不对应。

例如，它可能会返回：

 fromEmail: [e-mail], subject: hello
fromEmail: [e-mail], subject: welcome

当数据库中的记录是：

 fromEmail: [e-mail], subject: hello
fromEmail: [e-mail], subject: programming question
fromEmail: [e-mail], subject: welcome

如果“编程问题”主题是最新的，我如何让 MySQL 在对电子邮件进行分组时选择该记录？

原文由 John Kurlak 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

一个简单的解决方案是 先 使用 ORDER 语句将查询包装到子选择中，然后 再 应用 GROUP BY：

 SELECT * FROM (
    SELECT `timestamp`, `fromEmail`, `subject`
    FROM `incomingEmails`
    ORDER BY `timestamp` DESC
) AS tmp_table GROUP BY LOWER(`fromEmail`)

这类似于使用连接，但看起来要好得多。

在带有 GROUP BY 子句的 SELECT 中使用非聚合列是非标准的。 MySQL 通常会返回它找到的第一行的值并丢弃其余的值。任何 ORDER BY 子句仅适用于返回的列值，而不适用于丢弃的列值。

重要更新 选择用于在实践中工作但不应依赖的非聚合列。根据 MySQL 文档，“这主要是在每个未在 GROUP BY 中命名的非聚合列中的所有值对于每个组都相同时很有用。服务器可以 自由地从每个组中选择任何值，因此 除非它们相同，否则这些值选择是不确定的。”

从 5.7.5 开始，默认启用 ONLY_FULL_GROUP_BY，因此非聚合列会导致查询错误 (ER_WRONG_FIELD_WITH_GROUP)

正如@mikep 在下面指出的那样，解决方案是使用 5.7 及更高版本的 ANY_VALUE()

见 http://www.cafewebmaster.com/mysql-order-sort-group https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html https://dev.mysql .com/doc/refman/5.7/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value

原文由 b7kich 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

正如已经在回复中指出的那样，当前答案是错误的，因为 GROUP BY 从窗口中任意选择记录。

如果使用 MySQL 5.6 或 MySQL 5.7 和 ONLY_FULL_GROUP_BY ，则正确的（确定性）查询是：

 SELECT incomingEmails.*
  FROM (
    SELECT fromEmail, MAX(timestamp) `timestamp`
    FROM incomingEmails
    GROUP BY fromEmail
  ) filtered_incomingEmails
  JOIN incomingEmails USING (fromEmail, timestamp)
GROUP BY fromEmail, timestamp

为了使查询有效地运行，需要适当的索引。

请注意，为简化起见，我删除了 LOWER() ，在大多数情况下不会使用它。

原文由 Marcus 发布，翻译遵循 CC BY-SA 3.0 许可协议