mysql - Sql AVG included but getting Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY 子句

Question

我很困惑为什么会收到此错误:

"Column 'City.CityID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

我有两个表:一个名为City，其中包含城市信息，例如 id、名称等，另一个表 包含 cityid 和评级(可以有很多行具有相同的 cityid 和评级)。

我有一个城市的 group，我只是在 RateCity 表中取平均评级。那么为什么会失败呢？

select City.CityID, City.CityName, City.CityStateOrProvince, 
City.CityCountry, City.CityImageUri, AVG(RateCity.Rating) as AverageRating
from City
right join RateCity
on City.CityID = RateCity.CityID
group by City.CityID, RateCity.CityID

Answer 1

您需要按所有非聚合进行分组。

select City.CityID
      ,City.CityName
      ,City.CityStateOrProvince
      ,City.CityCountry
      ,City.CityImageUri
      ,AVG(RateCity.Rating) as AverageRating
from City 
right join RateCity on City.CityID = RateCity.CityID
 Group by 
       City.CityID
      ,City.CityName
      ,City.CityStateOrProvince
      ,City.CityCountry
      ,City.CityImageUri

另一种选择是

select City.CityID
      ,City.CityName
      ,CityStateOrProvince = max(City.CityStateOrProvince)
      ,CityCountry         = max(City.CityCountry)
      ,CityImageUri        = max(City.CityImageUri)
      ,AverageRating       = AVG(RateCity.Rating) 
from City 
right join RateCity on City.CityID = RateCity.CityID
 Group by 
       City.CityID
      ,City.CityName