mysql - 为什么在返回列的 MAX 值时总是返回 ID 最低的行？

Question

考虑这两个查询:

SELECT *, MAX(age) AS maxAge FROM someTable ORDER BY age ASC;
SELECT *, 'dummyC' AS dummyC FROM someTable ORDER BY age ASC;

前一个查询返回表的所有行和所有列，以及一个额外的虚拟列。后面的查询只返回一行，即具有最低主键的行。为什么会这样，我该如何解决？在一些旧的但稳定的 Debian 服务器上的 MySQL 5.1 中测试。

Answer 1

这是一个 MySQL extension .

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. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.

您获得的值(value)是不确定的。您通常会得到插入到表中的第一行，但这并不能保证。

如果您想要包含最高年龄的行中的相应值，那么最好结合使用 ORDER BY 和 LIMIT 1:

SELECT *
FROM someTable
ORDER BY age DESC
LIMIT 1;