SELECT 列表不在 GROUP BY 子句中，并且包含非聚合列

Question

收到以下错误：

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.country.Code' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

运行以下查询时：

select countrylanguage.language, country.code, sum(country.population*countrylanguage.percentage/100)
from countrylanguage
join country on countrylanguage.countrycode = country.code
group by countrylanguage.language
order by sum(country.population*countrylanguage.percentage) desc ;

使用 MySQL 世界测试数据库 （http://dev.mysql.com/doc/index-other.html）。不知道为什么会这样。当前运行 MYSQL 5.7.10。

任何想法??? ：O

Answer 1

正如@Brian Riley已经说过的那样，您应该删除选择中的1列

select countrylanguage.language ,sum(country.population*countrylanguage.percentage/100)
from countrylanguage
join country on countrylanguage.countrycode = country.code
group by countrylanguage.language
order by sum(country.population*countrylanguage.percentage) desc ;

或将其添加到您的分组中

select countrylanguage.language, country.code, sum(country.population*countrylanguage.percentage/100)
from countrylanguage
join country on countrylanguage.countrycode = country.code
group by countrylanguage.language, country.code
order by sum(country.population*countrylanguage.percentage) desc ;

Answer 2

> country.code不在 group by 语句中，也不是聚合（包装在聚合函数中）。

https://www.w3schools.com/sql/sql_ref_sqlserver.asp