Question

sql求某字段的count最大值，提示不允许出现分组函数

select code, count(code)
from database
where count(code) = (select max(count(code) ) from database)

请问应该如何修改，谢谢！

Answer 1

根据字段分组后才能查出来不同值存在的数量，比如 country_id=1的存在几个,=200的存在几个。

select country_id, count(country_id)  from users GROUP BY country_id;

结果

从大到小排序后取出第一条

select * from (select country_id, count(country_id) as country_count  from users

GROUP BY country_id) biao order by country_count desc limit 1

结果