mysql - 聚合函数需要GROUP BY吗？

Question

问题:

Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns. Do not include continents without cities in your output.

我的解决方案:

SELECT COUNTRY.CONTINENT, FLOOR(AVG(CITY.POPULATION)) FROM COUNTRY INNER JOIN CITY ON COUNTRY.CODE=CITY.COUNTRYCODE

但这似乎不起作用，直到我添加 GROUP BY 语句更新的解决方案:

SELECT COUNTRY.CONTINENT, FLOOR(AVG(CITY.POPULATION)) FROM COUNTRY INNER JOIN CITY ON COUNTRY.CODE=CITY.COUNTRYCODE GROUP BY COUNTRY.CONTINENT

为什么会这样呢？为什么不显示新 INNER JOIN 表的平均人口值？我知道这会给我错误的答案，即它将显示每个大陆相同的平均人口值。但我的疑问是，为什么当我不添加 GROUP BY 语句时它不起作用。

抛出的错误:

ERROR 1140 (42000) at line 1: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'run_y53padyvlle.COUNTRY.continent'; this is incompatible with sql_mode=only_full_group_by

Answer 1

问题不在于 AVG()。你可以这样做:

SELECT FLOOR(AVG(ci.POPULATION))
FROM COUNTRY c INNER JOIN
     CITY ci
     ON c.CODE = ci.COUNTRYCODE;

这将返回一行，它是数据库中所有城市的总体平均人口。

问题是当你这样做时:

SELECT c.CONTINENT, FLOOR(AVG(ci.POPULATION)) 
. . . 

CONTINENT 部分未聚合。 SQL 引擎需要知道如何处理它。将 key 放入 GROUP BY 中:

GROUP BY c.CONTINENT

表示您希望结果集中为 CONTINENT 中的每个值占一行。