mysql - Mysql:在表中获取最大的城市出现次数

我有一个表，其中包含一些数据和相应的城市，州。我希望看到某个特定州的城市最多。假设科罗拉多州丹佛市发生100次，科罗拉多州博尔德市发生10次，我只想看丹佛，因为那是最大的数目。我尝试了以下方法，但它也列出了所有其他城市：

select state, city, count(city)
from tab12
group by city
order by state, count(city);

任何帮助表示赞赏。谢谢。

最佳答案

SELECT state, city, max(cnt)
FROM
(select state, city, count(city) as cnt
 from tab12 group by city order by state, count(city))
order by state

如果您还想涵盖一些城市的频率相同的情况：

SELECT state, city, count(*) cnt
FROM cities GROUP BY state,city
HAVING cnt = (SELECT max(cnt)
              FROM
              (SELECT count(*) cnt FROM cities group by state,city) as tbl)
ORDER BY state;

关于mysql - Mysql:在表中获取最大的城市出现次数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11887598/