mysql - 在 SQL 中选择前 5 个计数最多的国家

Question

您好，我需要从 DB 中获取最多可数的国家/地区。它的样子:

id name country
1  fsdf Sweden
2  dfdf Brazil
3  fgfg Sweden
4  gfgg Germany
5  fgfg Germany
6  fgfg Poland
7  fdff Germany
8  iuii Brazil
9  tyyt Sweden
10 tyut Sweden
11 gfgf Germany
12 fdff Holland

我想从中得到输出——从最多的计数来看:

1 Germany count 4
2 Sweden count 4
3 Brazil count 2
4 Poland count 1
5 Holand coun 1

我试过类似的东西但没有用

$top5 = $mysqli -> query ("SELECT top 5 country, count(*)
from list_ots
group by country
order by country desc");

while ($row = mysql_fetch_assoc($top5)) {
    echo $row["country"];
}
mysql_free_result($result);

Answer 1

MySQL 中正确的语法使用LIMIT，而不是TOP:

select country, count(*) as cnt
from list_ots
group by country
order by cnt desc
limit 5;