MySQL“Group By”和“Order By” [英] MySQL "Group By" and "Order By"

问题描述

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

  SELECT 
`timestamp`，`fromEmail`，`subject` 
 FROM`incomingEmails` 
 GROUP BY LOWER（`fromEmail`）
 ORDER BY`timestamp` DESC 
  

查询几乎可以按照我的要求工作 - 它选择按电子邮件分组的记录。问题是主题和时间戳不对应特定电子邮件地址的最近记录。

例如，它可能会返回：

  fromEmail：[e-mail]，subject：hello 
 fromEmail：[e-mail]，subject：welcome 
  

当数据库中的记录为：

<$ p $来自电子邮件：[e-mail]，主题：hello
fromEmail：[e-mail]，主题：编程问题
from电子邮件：[e-mail]，主题：welcome

如果编程问题主题是最新的，我如何让MySQL选择该记录当分组电子邮件？

解决方案

一个简单的解决方案是用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子句只适用于返回的列值，而不适用于丢弃的列值。 b
$ b

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

从5.6.21开始，我发现在临时表上恢复ORDER BY排序的问题与GROUP BY有关。

自 5.7.5 默认情况下启用ONLY_FULL_GROUP_BY，即不可能使用非聚合列。

请参阅
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

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.

For example, it might return:

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

When the records in the database are:

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

If the "programming question" subject is the most recent, how can I get MySQL to select that record when grouping the e-mails?

解决方案

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.

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.

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."

As of 5.6.21 I have noticed issues with the GROUP BY on the temporary table reverting the ORDER BY sorting.

As of 5.7.5 ONLY_FULL_GROUP_BY is enabled by default, i.e. it's impossible to use non-aggregate columns.

See 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

这篇关于MySQL“Group By”和“Order By”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！