SELECT id HAVING id 的最大计数

2021-12-19 00:00:00 sql group-by select mysql having-clause

有一个带有 item_id 和 color_id 的产品表.我正在尝试使用最多的非空实例获取 color_id.

Have a products table with item_id and color_id. I'm trying to get the color_id with the most non-null instances.

这失败了:

SELECT color_id 
  FROM products 
 WHERE item_id=1234 
 GROUP BY item_id 
HAVING MAX(COUNT(color_id))

与

Invalid use of group function

这个

SELECT color_id, COUNT(color_id)
  FROM products 
 WHERE item_id=1234 
 GROUP BY item_id

退货

color_id count
1, 323
2, 122
3, 554

我正在寻找 color_id 3，它拥有最多的实例.

I am looking for color_id 3, which has the most instances.

是否有一种快速简便的方法无需 2 次查询即可获得我想要的东西?

Is there a quick and easy way of getting what I want without 2 queries?

推荐答案

SELECT color_id AS id, COUNT(color_id) AS count 
FROM products 
WHERE item_id = 1234 AND color_id IS NOT NULL 
GROUP BY color_id 
ORDER BY count DESC
LIMIT 1;

这将为您提供 color_id 和该 color_id 的计数，按计数从最大到最小排序.我想这就是你想要的.

This will give you the color_id and the count on that color_id ordered by the count from greatest to least. I think this is what you want.

为了您的编辑...

SELECT color_id, COUNT(*) FROM products WHERE color_id = 3;

相关文章