如何使用MAX()返回具有最大值的行？

Question

我有一个表格orders，其中包含字段id、customer_id和amt：

SQL Fiddle

我想获取具有最大值的amt的customer_id。

我做出了以下查询：

SELECT customer_id, MAX(amt) FROM orders;

但是这个查询的结果包含了一个错误的customer_id值。

然后我构建了如下的查询：

SELECT customer_id, MAX(amt) AS maximum FROM orders GROUP BY customer_id ORDER BY maximum DESC LIMIT 1;

并且得到了正确的结果。

但是我不理解为什么我的第一个查询没有正常工作。我做错了什么？

而且是否可能以更简单、更有效的方式更改我的第二个查询，使我获得必要的信息？

- db learner

Answer 1

某些版本的SQL在您选择字段时，如果使用聚合运算符如MAX或SUM，并且您选择的字段没有出现在GROUP BY中，则会给出警告或错误。

要获取与最大amt相对应的customer_id需要更复杂的查询。不幸的是，SQL并不像您想象的那么简单。一种做法是：

select customer_id from orders where amt = ( select max(amt) from orders);

虽然使用连接（join）的解决方案可能更加高效。

为了理解为什么您尝试做的事情没有意义，请将 MAX 替换为 SUM。从聚合运算符的解释角度来看，MAX 返回与实际行对应的内容仅仅是巧合。例如，SUM 就没有这个属性。

- djechlin

---

我认为这是最好的解决方案，但请注意它将返回所有拥有最大金额的客户；换句话说，您可能会得到多行数据。 - BellevueBob

Answer 2

MySQL允许您在查询中省略GROUP BY，从而返回带有任意customer_id的整个表中MAX(amt)。大多数其他RDBMS在使用聚合时需要GROUP BY子句。

我认为您的第二个查询没有任何问题 - 还有其他方法可以做到，但是您的查询也可以正常工作。

- sgeddes

---

第二个查询的问题在于它只会返回一个客户（可能存在并列情况）。这对于 OP 可能没问题，但应该指出来。 - BellevueBob

Answer 3

MAX()存在的问题在于它只会选择指定字段中的最高值，而不考虑同一行中的其他值或偏好。MySQL通常会返回GROUP的第一行的任何值（在这种情况下，GROUP由整个表组成，因为没有指定分组），在聚合过程中丢弃其他行的信息。

为了解决这个问题，你可以这样做：

SELECT customer_id, amt FROM orders ORDER BY amt DESC LIMIT 1

它应该返回您的customer_id和最高amt，同时保留两者之间的关系，因为没有进行聚合。

- Havenard

Answer 4

实际上，你的第一个查询可以被看作是将所有内容归为一组进行GROUP BY处理。 此外，MySQL可以自由选择来自同一组不同源行的每个输出值。

http://dev.mysql.com/doc/refman/5.7/en/group-by-extensions.html

MySQL扩展了GROUP BY的使用，以便选择列表可以引用未在GROUP BY子句中命名的非聚合列。服务器可以从每个组中选择任何值，因此除非它们相同，否则所选的值是不确定的。此外，无法通过添加ORDER BY子句来影响从每个组中选择的值。结果集的排序发生在选择值之后，ORDER BY不会影响服务器选择每个组中的哪些值。

- biziclop