从GROUP BY获取MAX [英] Get MAX from a GROUP BY

问题描述

当我碰到一些SQL时，我正在练习一些SQL。我想看看某种商品出现了多少次，并从那里得到最多的商品。

这表明有多少每个商品出现的次数：

  mysql> SELECT商品，COUNT（商品）计数FROM订单GROUP BY商品ORDER BY计数; 
 + ---------------------- + ------------ + 
 |商品| count | 
 + ---------------------- + ------------ + 
 | PERSIAN MELON | 4 | 
 |豆类| 6 | 
 | CASABA | 10 | 
 | ASPARAGUS | 11 | 
 | EGGPLANT | 12 | 
 |西红柿，樱桃| 16 | 
 | GALIA MELON | 18 | 
 + ----------------------------------- + 
  

我试图获得最高的行，但都是错的：

 的MySQL> SELECT商品，MAX（COUNT（商品））FROM FROM GROUP BY商品ORDER BY count; 
  

这样做的正确方法是什么？

COUNT

  SELECT商品，COUNT（商品）`count` 
 FROM订单
 GROUP BY商品
 ORDER BY`count` DESC 
 LIMIT 1 
  

但是这样做，

<$ p $ （商品）=
（
） SELECT商品，COUNT（商品）`count`
FROM订单
GROUP BY商品
SELECT MAX（`COUNT`）
FROM
（
SELECT COUNT（商品）`count`
FROM orders
GROUP BY商品
）s
）


I was practicing some SQL when this hit me. I wanted to see how many times a certain commodity came up and from there get the commodity which came up the most.

This shows how many times each commodity comes up:

mysql> SELECT commodity, COUNT(commodity) count FROM orders GROUP BY commodity ORDER BY count;
+----------------------+------------+
| commodity            |    count   |
+----------------------+------------+
| PERSIAN MELON        |          4 |
| BEANS                |          6 |
| CASABA               |         10 |
| ASPARAGUS            |         11 |
| EGGPLANT             |         12 |
| TOMATOES, CHERRY     |         16 |
| GALIA MELON          |         18 |
+-----------------------------------+

I'm trying to get the row with the highest but it's all wrong:

mysql> SELECT commodity, MAX(COUNT(commodity)) count FROM orders GROUP BY commodity ORDER BY count;

What's the right way of doing this?

解决方案

CAUTION: the query will not handle duplicate records having the maximum COUNT

SELECT  commodity,  COUNT(commodity) `count` 
FROM    orders 
GROUP   BY commodity
ORDER   BY `count` DESC 
LIMIT   1

But this will,

SELECT  commodity,  COUNT(commodity) `count` 
FROM    orders 
GROUP   BY commodity
HAVING  COUNT(commodity) =
(
    SELECT MAX(`COUNT`) 
    FROM
    (
        SELECT  COUNT(commodity) `count` 
        FROM    orders 
        GROUP   BY commodity
    )   s
)

这篇关于从GROUP BY获取MAX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！