MySQL“ Group By”和“ Order By”

我希望能够从一个电子邮件表中选择一组行，并按发件人对它们进行分组。我的问题是这样的:

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

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

254761 次浏览
小开

这里有一个方法:

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
小开

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

ONLY _ FULL _ GROUP _ BY

您应该首先选择 from Email，MIN (read) ，然后使用第二个查询(或子查询)-Subject。

小开
最佳答案

一个简单的解决方案是使用 ORDER 语句 第一将查询包装到子选择中，并应用 GROUP BY 回见:

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

这类似于使用联接，但是看起来好多了。

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

重要更新 选择在实践中可以使用但不应依赖的非聚合列。根据 MySQL 文档，“当每个 GROUPBY 中未命名的每个非聚合列中的所有值对于每个组都相同时，这主要是有用的。服务器是来自每个组的 自由选择任何价值，所以是 unless they are the same, the values chosen are indeterminate。”

从 5.7.5开始，默认情况下启用 ONLY _ FULL _ GROUP _ BY，因此非聚合列会导致查询错误(ER _ WRONG _ FIELD _ WITH _ GROUP)

正如@mikep 在下面指出的，解决方案是使用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

小开

对于比上面显示的查询更复杂的查询，我在这两种方法中都遇到了困难，因为无论我放置什么索引，子查询方法都非常低效，而且因为我无法通过 Hibernate 获得外部自连接

实现这一点的最佳(也是最简单)方法是按照构造来包含所需字段串联的内容进行分组，然后使用 SELECT 子句中的表达式将它们提取出来。如果需要执行 MAX () ，请确保要 MAX ()覆盖的字段始终位于连接实体的最重要端。

理解这一点的关键是，只有当这些其他字段对于满足 Max ()的任何实体都是不变的时候，查询才有意义，所以从排序的角度来看，串联的其他部分可以被忽略。它在链接的最底部解释了如何做到这一点。http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

如果你能让 am insert/update 事件(像触发器一样)来预先计算字段的串联，你就可以索引它，查询的速度就像你实际想要的字段一样快。您甚至可以使用它来获取多个字段的最大值。我使用它对表示为嵌套集的多维树进行查询。

小开

在 ORDERBY 之后执行 GROUP BY，方法是像下面这样用 GROUP BY 包装查询:

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

As pointed in a reply already, the current answer is wrong, because the GROUP BY arbitrarily selects the record from the window.

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

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()，它在大多数情况下不会被使用。


提交答案