mysql - 按两个字段分组后查找 MAX(Avg(value))

Question

表名称为c_list。

Country   City      Rating   Date
------------------------------------
France    Brest     95       24092016       
France    Brest     98       27092016
France    Brest     95       03102016
France    Lille     100      26092016     
France    Lille     92       28092016
Japan     Tokyo     98       02102016

有 50 多个不同的国家，每个国家都有几个城市。并且每个城市可能有不止一行或更多记录。我想选择一个平均评分最高的城市(与自己国家/地区的城市比较)，然后与不同国家/地区的所有其他城市进行比较。因此，最终查询应使用 max(avg(Rating)) 并按降序显示所有国家/地区及其一个城市。示例输出:

Country   City        max(avg(rating))   
-------------------------------------
 USA      New York    97.25
 UK       Cardiff     96.70
 Germany  Greven      96.50
 Turkey   Afyon       94.88
 France   Guipavas    94.10
 Canada   Cartwright  91.35              

我只能获得一个国家/地区的最大值(平均(评级))。需要帮助。

SELECT top 1 country, city, Avg(rating) AS Ratings
FROM c_list
where country = 'France'
GROUP BY city, country
order by Ratings desc

(已编辑)我想要的结果类似于世界小姐比赛。首先与您所在国家/地区的本地参赛者竞争并获胜。接下来(我的最终结果集)是与其他国家/地区的获胜者竞争，并使用他们在自己国家/地区获得的平均(评级)将他们排名第一到最后。

Answer 1

如果我没记错的话，你正在寻找这个

SELECT country, 
       city, 
       Avg(rating) AS Ratings 
FROM   c_list A 
GROUP  BY city, 
          country 
HAVING Avg(rating) = (SELECT TOP 1 Avg(rating) AS Ratings 
                      FROM   c_list B 
                      WHERE  a.country = b.country 
                      GROUP  BY city 
                      ORDER  BY ratings DESC) 
ORDER  BY ratings DESC 

注意:如果您使用Mysql，请将TOP关键字替换为LIMIT