本文介绍了SELECT id HAVING id 的最大计数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
有一个带有 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;
这篇关于SELECT id HAVING id 的最大计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！