为什么在返回列的 MAX 值时总是返回最低 ID 行? [英] Why is the lowest-ID row always returned when also returning a MAX value for a column?

问题描述

考虑这两个查询:

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 中测试.

The former query returns all rows and all columns of the table, plus an additional dummy column. The later query returns only a single row, that row which has the lowest primary key. Why is that, and how can I work around it? Tested in MySQL 5.1 on some old but stable Debian server.

推荐答案

这是一个MySQL 扩展.

MySQL 扩展了 GROUP BY 的使用，以便选择列表可以引用未在 GROUP BY 子句中命名的非聚合列.这意味着前面的查询在 MySQL 中是合法的.您可以使用此功能通过避免不必要的列排序和分组来获得更好的性能.但是，这主要在未在 GROUP BY 中命名的每个非聚合列中的所有值对于每个组都相同时很有用.服务器可以自由地从每个组中选择任何值，因此除非它们相同，否则选择的值是不确定的.此外，添加 ORDER BY 子句不会影响从每个组中选择值.结果集的排序发生在选择值之后，ORDER BY 不影响服务器选择哪些值.

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.

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

The value you get is indeterminate. You will often get the first row that was inserted into the table, but this is not guaranteed.

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

If you want the corresponding values from the row containing the highest age then it is better to use a combination of ORDER BY and LIMIT 1:

SELECT *
FROM someTable
ORDER BY age DESC
LIMIT 1;

这篇关于为什么在返回列的 MAX 值时总是返回最低 ID 行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！