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
