本文介绍了从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 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!
10-13 19:28