mysql ORDER results BY 列中的出现次数

Question

我有一个数据库，它将接收一大堆信息，但最重要的是，它会为每一行接收一个城市名称。示例如下:

id         city
1          London
2          Manchester
3          London
4          Brighton
5          Oxford
6          Oxford
7          London

我只想从此表中选择城市名称，按最常出现的顺序显示前 3 个结果。

到目前为止，我正在使用我发现的下面的查询:

SELECT N.city, COUNT(*) AS howmany
FROM ( SELECT DISTINCT city FROM events ) AS N,
events AS T
WHERE N.city = T.city
GROUP BY N.city
ORDER BY howmany
LIMIT 0,2 

Answer 1

您可以将现有查询简化为对事件表的单一访问，而无需将其连接到自身:

SELECT N.city, 
       COUNT(N.city) AS howmany 
  FROM events AS N
 GROUP BY N.city 
 ORDER BY howmany 
 LIMIT 3

如果您不想返回计数，请尝试:

SELECT N.city
  FROM events AS N
 GROUP BY N.city 
 ORDER BY COUNT(N.city)
 LIMIT 3