MySQL“分组依据"和“订购者"
我希望能够从电子邮件表中选择一堆行，然后按发件人对它们进行分组.我的查询如下所示:
I want to be able to select a bunch of rows from a table of e-mails and group them by the from sender. My query looks like this:
SELECT
`timestamp`, `fromEmail`, `subject`
FROM `incomingEmails`
GROUP BY LOWER(`fromEmail`)
ORDER BY `timestamp` DESC
该查询几乎可以按我的意愿工作——它选择按电子邮件分组的记录.问题在于主题和时间戳与特定电子邮件地址的最新记录不对应.
The query almost works as I want it — it selects records grouped by e-mail. The problem is that the subject and timestamp don't correspond to the most recent record for a particular e-mail address.
例如，它可能会返回:
fromEmail: john@example.com, subject: hello
fromEmail: mark@example.com, subject: welcome
当数据库中的记录为:
fromEmail: john@example.com, subject: hello
fromEmail: john@example.com, subject: programming question
fromEmail: mark@example.com, subject: welcome
如果编程问题"主题是最新的，我如何让 MySQL 在对电子邮件进行分组时选择该记录?
If the "programming question" subject is the most recent, how can I get MySQL to select that record when grouping the e-mails?
推荐答案
一个简单的解决方案是使用 ORDER 语句 first 将查询包装到一个子选择中，然后应用 GROUP BY :
A simple solution is to wrap the query into a subselect with the ORDER statement first and applying the GROUP BY later:
SELECT * FROM (
SELECT `timestamp`, `fromEmail`, `subject`
FROM `incomingEmails`
ORDER BY `timestamp` DESC
) AS tmp_table GROUP BY LOWER(`fromEmail`)
这类似于使用连接，但看起来更好.
This is similar to using the join but looks much nicer.
在带有 GROUP BY 子句的 SELECT 中使用非聚合列是非标准的.MySQL 通常会返回它找到的第一行的值并丢弃其余的.任何 ORDER BY 子句仅适用于返回的列值，而不适用于丢弃的列值.
Using non-aggregate columns in a SELECT with a GROUP BY clause is non-standard. MySQL will generally return the values of the first row it finds and discard the rest. Any ORDER BY clauses will only apply to the returned column value, not to the discarded ones.
重要更新选择过去在实践中工作但不应该依赖的非聚合列.根据 MySQL 文档 "这很有用主要是当每个未在 GROUP BY 中命名的非聚合列中的所有值对于每个组都相同时.服务器可以自由地从每个组中选择任何值，因此除非它们相同，否则选择的值是不确定的."
IMPORTANT UPDATE Selecting non-aggregate columns used to work in practice but should not be relied upon. Per the MySQL documentation "this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate."
截至 5.7.5 ONLY_FULL_GROUP_BY默认启用，因此非聚合列会导致查询错误 (ER_WRONG_FIELD_WITH_GROUP)
As of 5.7.5 ONLY_FULL_GROUP_BY is enabled by default so non-aggregate columns cause query errors (ER_WRONG_FIELD_WITH_GROUP)
正如@mikep 在下面指出的，解决方案是使用 ANY_VALUE() 来自 5.7 及更高版本
As @mikep points out below the solution is to use ANY_VALUE() from 5.7 and above
见http://www.cafewebmaster.com/mysql-order-sort-grouphttps://dev.mysql.com/doc/refman/5.6/en/group-by-handling.htmlhttps://dev.mysql.com/doc/refman/5.7/en/group-by-handling.htmlhttps://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value
相关文章