MySQL SELECT id HAVING id 的最大计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14249689/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT id HAVING maximum count of id
提问by a coder
Have a products table with item_id and color_id. I'm trying to get the color_id with the most non-null instances.
有一个带有 item_id 和 color_id 的产品表。我正在尝试使用最多的非空实例获取 color_id。
This fails:
这失败了：
SELECT color_id
FROM products
WHERE item_id=1234
GROUP BY item_id
HAVING MAX(COUNT(color_id))
with
和
Invalid use of group function
This
这个
SELECT color_id, COUNT(color_id)
FROM products
WHERE item_id=1234
GROUP BY item_id
Returns
退货
color_id count
1, 323
2, 122
3, 554
I am looking for color_id 3, which has the most instances.
我正在寻找 color_id 3，它有最多的实例。
Is there a quick and easy way of getting what I want without 2 queries?
有没有一种快速简便的方法可以在没有 2 个查询的情况下获得我想要的东西？
回答by matt walters
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;
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.
这将为您提供 color_id 和该 color_id 的计数，按计数从最大到最小排序。我想这就是你想要的。
for your edit...
为您的编辑...
SELECT color_id, COUNT(*) FROM products WHERE color_id = 3;
回答by John Woo
SELECT color_id
FROM
(
SELECT color_id, COUNT(color_id) totalCount
FROM products
WHERE item_id = 1234
GROUP BY color_id
) s
HAVING totalCount = MAX(totalCount)
UPDATE 1
更新 1
SELECT color_id, COUNT(color_id) totalCount
FROM products
WHERE item_id = 1234
GROUP BY color_id
HAVING COUNT(color_id) =
(
SELECT COUNT(color_id) totalCount
FROM products
WHERE item_id = 1234
GROUP BY color_id
ORDER BY totalCount DESC
LIMIT 1
)
回答by drneel
SELECT
color_id,
COUNT(color_id) AS occurances
FROM so_test
GROUP BY color_id
ORDER BY occurances DESC
LIMIT 0, 1
Here is a sample fiddle with a basic table that shows it working: sql fiddle
这是一个带有基本表的示例小提琴，显示它正在工作：sql fiddle
