SELECT
Continent,
COUNT(*) AS CountriesCount,
MIN(Population) AS MinPopulation,
MAX(Population) AS MaxPopulation,
AVG(Population) AS AvgPopulation
FROM country
GROUP BY Continent
| Continent |
CountriesCount |
MinPopulation |
MaxPopulation |
AvgPopulation |
| Asia |
51 |
286,000 |
1,277,558,000 |
72,647,562.74 |
| Europe |
46 |
1000 |
146,934,000 |
15,871,186.95 |
| North America |
37 |
7,000 |
278,357,000 |
13,053,864.86 |
| Africa |
58 |
0 |
111,506,000 |
13,525,431.03 |
| Oceania |
28 |
0 |
18,886,000 |
1,085,755.35 |
| Antarctica |
5 |
0 |
0 |
0.00 |
| South America |
14 |
2000 |
170,115,000 |
24,698,571.42 |
| GROUP BY 子句让数据库按 Continent 分组，于是 CountriesCount 是按每个 Continent 各算一次，而不是整张表算一次。
GROUP BY 要写在 WHERE 之后、ORDER BY 之前。 |