MySQL的“Group By”和“Order By”

Question

MySQL的“Group By”和“Order By”

mysqlsqlgroup-bysql-order-byaggregate-functions

119

我想要从一个包含多封电子邮件的表格中选择一组行，并按发送者进行分组。我的查询语句如下：

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

这个查询几乎符合我的需求 - 它选择按电子邮件分组的记录。问题是主题和时间戳不对应于特定电子邮件地址的最新记录。

例如，它可能返回：

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选择该记录？

- John Kurlak

6个回答

53

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

如果使用MySQL 5.6或带有ONLY_FULL_GROUP_BY的MySQL 5.7，则正确（确定性）的查询语句为：

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

3

这应该是正确的答案。我刚发现了一个与此相关的网站漏洞。其他答案中子查询中的 order by 根本没有起作用。 - Jette

1

天啊，请把这个设为被采纳的答案。那个被采纳的浪费了我五个小时的时间 :( - Richard

我喜欢这个答案，但最后还需要排序。 - b7kich

46

以下是一种方法：

SELECT cur.textID, cur.fromEmail, cur.subject, 
     cur.timestamp, cur.read
FROM incomingEmails cur
LEFT JOIN incomingEmails next
    on cur.fromEmail = next.fromEmail
    and cur.timestamp < next.timestamp
WHERE next.timestamp is null
and cur.toUserID = '$userID' 
ORDER BY LOWER(cur.fromEmail)

基本上，你需要将表连接到自身，搜索之后的行。在where子句中说明不能有更晚的行。这样就只会给你最新的一行。

如果有多个时间戳相同的电子邮件，则需要优化此查询。如果电子邮件表中有一个递增的ID列，请像下面这样更改JOIN语句：

LEFT JOIN incomingEmails next
    on cur.fromEmail = next.fromEmail
    and cur.id < next.id

- Andomar

说 textID 不明确 =/ - John Kurlak

1

然后消除歧义并使用表名称作为前缀，如cur.textID。回答中也进行了更改。 - Andomar

1

这是使用Doctrine DQL可能实现的唯一解决方案。 - VisioN

当您尝试为多个列进行自连接时，这种方法可能无法正常工作。例如，当您尝试查找最新的电子邮件和最新的用户名，并且需要多个自连接才能在单个查询中执行此操作时。 - Loveen Dyall

在处理过去和未来的时间戳/日期时，为了将结果集限制为非未来日期，您需要向“LEFT JOIN”条件添加另一个条件：“AND next.timestamp <= UNIX_TIMESTAMP()”。 - Will B.

32

在ORDER BY之后进行GROUP BY，可以通过将查询语句包装在GROUP BY中来实现：

SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t GROUP BY t.from

- 11101101b

1

那么 GROUP BY 自动选择最新的 time，还是最新的 time，或者是随机的？ - xrDDDD

1

它选择最新的时间，因为我们按time DESC排序，然后分组取第一个（最新的）。 - 11101101b

现在如果我能在mysql 5.1的视图中对子查询进行联接就好了。也许这个功能会在更新的版本中出现。 - IcarusNM

22

根据SQL标准，您不能在选择列表中使用非聚合列。MySQL允许这种用法（除非使用ONLY_FULL_GROUP_BY模式），但结果是不可预测的。

您应该先选择fromEmail、MIN(read)，然后再使用第二个查询（或子查询）选择Subject。

注：ONLY_FULL_GROUP_BY模式详见此链接。

- noonex

MIN(read)会返回“read”中的最小值。他可能正在寻找最新电子邮件的“read”标志。 - Andomar

4

对于比展示的更加复杂的查询，我曾经尝试过两种方法，但是子查询方法无论我在哪里创建索引都效率极低，并且我无法通过Hibernate实现外部自连接。

最好（也是最简单）的方法是按照你需要的字段构造一个包含这些字段的串，然后通过SELECT语句中的表达式将它们提取出来。如果你需要使用MAX()函数，请确保你想要使用MAX()的字段始终位于串的最重要的位置。

理解这一点的关键在于，查询只有在这些其他字段对于满足Max()的任何实体都是不变的情况下才有意义，因此在排序方面，可以忽略串的其他部分。请参见此链接底部的说明：http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

如果你可以获取插入/更新事件（如触发器）来预计算字段的串，则可以对其进行索引，并且查询速度将与仅针对实际想要使用MAX()的字段进行分组相同。您甚至可以使用它来获取多个字段的最大值。我用它来对作为嵌套集合表示的多维树进行查询。

- Mike N

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- b7kich · Accepted Answer

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

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

这类操作和使用join相似，但看起来更加简洁美观。

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

重要更新： 以前可以选择非聚合列，在实践中也可以正常工作，但不应该依赖这种方法。根据MySQL文档，“仅当未在GROUP BY子句中命名的每个非聚合列中的所有值对于每个组都相同时，此方法才有用。服务器可以从每个组中选择任何值，因此除非它们是相同的，否则所选的值是不确定的。”

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

如下所述，解决方案是在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