选择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;

Answer 2

必须使用LIMIT才能获得前5名：

SELECT country, count(*) as count
FROM list_ots
GROUP BY country
ORDER by count desc
LIMIT 5;

Answer 3

选择前5个国家，计数(*)为CountryCount从list_ots集团按国家顺序按CountryCount Desc