按特定分组的最大值 [英] Group-wise Maximum of a Certain Column

问题描述

我有这张表：

  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.

这篇关于按特定分组的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！