MySQL GROUP BY 行为(使用带排序依据的派生表时)

Question

由于 mysql 不强制执行单值规则(请参阅:https://stackoverflow.com/a/1646121/1688441)，具有排序依据的派生表是否保证将显示哪些行值？这适用于不在聚合函数中且不在 group by 中的列。

在评论并回答了问题 (MySQL GROUP BY behavior) 之后，我正在查看问题 (https://stackoverflow.com/a/24653572/1688441)。

我不同意接受的答案，但意识到可能改进的答案是:

SELECT * FROM 
(SELECT * FROM tbl order by timestamp) as tb2
GROUP BY userID;

http://sqlfiddle.com/#!2/4b475/18

这是正确的还是 mysql 仍会任意决定显示哪些行值？

Answer 1

这个查询:

SELECT *
FROM (SELECT * FROM tbl order by timestamp) as tb2
GROUP BY userID;

通过扩展依赖于 MySQL 组，已记录 here .您特别依赖这样一个事实，即所有列都来自同一行并且遇到的第一行。 MySQL 特别警告不要做出这种假设:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, 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.

因此，您不能依赖此行为。这很容易变通。这是一个示例查询:

select t.*
from tbl t
where not exists (select 1 from tbl t2 where t2.userid = t.userid and t2.timestamp > t.timestamp)

有了 tbl(userid, timestamp) 上的索引，这甚至可以更快地工作。众所周知，MySQL 在优化聚合方面做得很差。