MySQL count + rollup 对查询问题进行小计

Question

我的程序将从按排名降序排序的表中获取前 100 行。

SELECT city FROM table ORDER BY rank DESC LIMIT 0,100

返回:

Seattle,
Portland,
Los Angeles,
Portland,
Eugene,
Eugene,
etc...

然后我想计算每个城市在结果集中出现的频率(它必须是按排名降序排序的 100 个城市的部分集，而不是整个表。)

我想要什么:

Portland, 2
Eugene, 2
Seattle, 1
Los Angeles, 1

我不太确定如何编写该查询，请帮忙。

非常感谢!

Answer 1

SELECT city, count(*)
FROM
(
  SELECT city FROM table ORDER BY rank DESC LIMIT 0,100
) as sub
GROUP BY city
ORDER BY COUNT(*) DESC