mysql是依据什么进行分组_sql - MySQL“分组依据”和“排序依据”

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

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

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

6个解决方案

131 votes

一个简单的解决方案是首先使用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文档“这主要是当每个非GROUPAG中未命名的非聚合列中的所有值对于每个组都相同时。服务器可以自由选择每个组中的任何值，因此除非它们相同，否则值 被选中是不确定的。“

从5.6.21开始，我注意到临时表上GROUP BY的问题还原了ORDER 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]

b7kich answered 2019-09-02T17:17:52Z

41 votes

这是一种方法：

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 answered 2019-09-02T17:18:28Z

29 votes

通过使用GROUP BY包装查询，在ORDER BY之后执行GROUP BY：

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

11101101b answered 2019-09-02T17:18:51Z

23 votes

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

如果使用MySQL 5.6或MySQL 5.7和LOWER()，则正确的(确定性)查询是：

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 answered 2019-09-02T17:19:35Z

21 votes

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

ONLY_FULL_GROUP_BY

您应首先选择fromEmail，MIN(读取)，然后选择第二个查询(或子查询) - Subject。

noonex answered 2019-09-02T17:20:13Z

2 votes

对于比这些更复杂的查询，我努力使用这两种方法，因为无论我使用什么索引，子查询方法都非常缺乏，并且因为我无法通过Hibernate获得外部自连接

执行此操作的最佳(也是最简单)方法是按构建的内容进行分组，以包含所需字段的串联，然后使用SELECT子句中的表达式将它们拉出。 如果需要执行MAX()，请确保您想要MAX()的字段始终位于连接实体的最重要的末尾。

理解这一点的关键是，只有当这些其他字段对于满足Max()的任何实体都是不变的时，查询才有意义，因此就排序而言，可以忽略其他部分的连接。 它解释了如何在此链接的最底部执行此操作。[http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html]

如果您可以获得插入/更新事件(如触发器)来预先计算字段的串联，则可以将其编入索引，并且查询将如同分组仅仅是您实际想要MAX的字段一样快()。 您甚至可以使用它来获取最多的多个字段。 我用它来对表达为嵌套集的多维树进行查询。

Mike N answered 2019-09-02T17:21:00Z