本文介绍了从GROUP BY获取MAX的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
当我碰到一些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 1But 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
发布评论