本文介绍了从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
发布评论