MySQL SQL 选择最常见的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9853671/
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
SQL Select most common values
提问by lots_of_questions
I'm pretty new to SQL (I'm using MySQL) and need some help. I'm currently trying to select the most common age(s) from a table called PERSON. Suppose PERSON has an AGE column which has values: 10, 10, 20, 20, 30. The query should return the values 10 and 20.
我对 SQL 很陌生（我正在使用 MySQL）并且需要一些帮助。我目前正在尝试从名为 PERSON 的表中选择最常见的年龄。假设 PERSON 有一个 AGE 列，它的值是：10、10、20、20、30。查询应该返回值 10 和 20。
The following query only retrieves the top row (20):
以下查询仅检索顶行 (20)：
SELECT AGE FROM PERSON GROUP BY AGE ORDER BY COUNT(*) DESC LIMIT 1;
My other thought was to try something like:
我的另一个想法是尝试类似的事情：
SELECT AGE FROM PERSON GROUP BY AGE HAVING COUNT(AGE) = MAX(COUNT(AGE));
This returns an error, stating that it is invalid use of group function.
这将返回一个错误，指出它是无效使用组功能。
Any help would be greatly appreciated. Thanks!
任何帮助将不胜感激。谢谢！
回答by Castilho
SELECT *, COUNT(AGE) as age_count
FROM PERSON
GROUP BY AGE
ORDER BY age_count DESC
LIMIT 1
Can't test it here but it should work.
不能在这里测试，但它应该可以工作。
回答by Mosty Mostacho
This will do:
这将：
select age from persons
group by age
having count(*) = (
select count(*) from persons
group by age
order by count(*) desc
limit 1)
回答by Glenn
WITH x AS (
SELECT age, COUNT(*) numOfAge
FROM person
GROUP BY age
)
SELECT age
FROM x
WHERE numOfAge = ( SELECT MAX(numOfAge) FROM x)
ORDER BY age
