SQL选择最常见的值 [英] SQL Select most common values

问题描述

我刚接触SQL(我正在使用MySQL)，并且需要一些帮助.我目前正在尝试从称为PERSON的表中选择最常见的年龄.假设PERSON的AGE列的值为:10、10、20、20、30.查询应返回值10和20.

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.

以下查询仅检索第一行(20):

The following query only retrieves the top row (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!

推荐答案

这可以做到:

select age from persons
group by age
having count(*) = (
  select count(*) from persons
  group by age
  order by count(*) desc
  limit 1)

这篇关于SQL选择最常见的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！