按特定分组的最大值

本文介绍了按特定分组的最大值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这张表：

SELECT * FROM shop; + --------- + -------- + ------ |文章|经销商|价格 + --------- + -------- + ------ | 0001 | A | 3.45 | 0001 | B | 3.99 | 0002 | A | 10.99 | 0003 | B | 1.45 | 0003 | C | 1.69 | 0003 | D | 1.25 | 0004 | D | 19.95 + --------- + -------- + ------ 7行（0.20秒）

我想得到 - 对于每篇文章 - 价格最昂贵的经销商或经销商。

任何人都可以告诉我为什么这不起作用吗？

SELECT article，dealer，MAX（price ）FROM shop GROUP BY（article）;

对于此查询，我得到以下结果集;

+ --------- + -------- + ------ ------ ------ + |文章|经销商| MAX（价格）| + --------- + -------- + ------------ + | 0001 | A | 3.99 | | 0002 | A | 10.99 | | 0003 | B | 1.69 | | 0004 | D | 19.95 | + --------- + -------- + ------------ + 4行（0.03秒）

尽管最高价格是正确的，但我在某些物品上找到了错误的经销商。

解决方案

标准SQL会拒绝您的查询，因为您不能 SELECT 非非聚合字段聚合查询中 GROUP BY 子句的一部分。

您正在使用SQL的MySQL扩展 here ：

MySQL扩展了GROUP BY的用法选择列表可以引用未在GROUP BY子句中命名的 nonaggregated列。这意味着前面的查询在MySQL中是合法的。您可以使用此功能来避免不必要的列排序和分组，以获得更好的性能。但是，这对于每个非集合列中未在GROUP BY中命名的所有值对于每个组都是相同的都是有用的。 服务器可以自由选择每组中的任何值，因此除非相同，否则所选值不确定

。

I've got the table:

SELECT * FROM shop; +---------+--------+------ | article | dealer | price +---------+--------+------ | 0001 | A | 3.45 | 0001 | B | 3.99 | 0002 | A | 10.99 | 0003 | B | 1.45 | 0003 | C | 1.69 | 0003 | D | 1.25 | 0004 | D | 19.95 +---------+--------+------ 7 rows in set (0.20 sec)

And I want to get - for each article - the dealer or dealers with the most expensive price.

Could anyone tell me why this doesn’t work?

SELECT article, dealer, MAX(price) FROM shop GROUP BY(article);

For this query, I get the following result-set;

+---------+--------+------------+ | article | dealer | MAX(price) | +---------+--------+------------+ | 0001 | A | 3.99 | | 0002 | A | 10.99 | | 0003 | B | 1.69 | | 0004 | D | 19.95 | +---------+--------+------------+ 4 rows in set (0.03 sec)

Although the max prices are correct, I got the wrong dealers for some articles.

解决方案

Standard SQL would reject your query because you can not SELECT non-aggregate fields that are not part of the GROUP BY clause in an aggregate query.

You're using a MySQL extension of SQL described here:

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.