php - 如何在 MySQL 中选择 TOP n 分组值的 AVG？

Question

数据库:

player  |  team  |  points  
player1 | team1  |  100
player2 | team1  |  90
player3 | team2  |  100
player4 | team2  |  95
player5 | team2  |  90

我试图从每支球队的积分中获得前 2 名球员，并将他们平均计算到球队排名中，同时在查询中对球队进行分组:

team2 97.5 (not 95)
team1 95


       `$mysqli->query("SELECT charGuild, gr FROM (
        SELECT charGuild, AVG(charRating) as gr 
        FROM ins_rated 
        GROUP BY charGuild 
        HAVING COUNT(*) >= 10
        ORDER BY gr DESC
        LIMIT 15
        )
       ORDER BY gr DESC
       LIMIT 40");`

没有按预期工作。

$mysqli->query("SELECT charGuild, AVG(charRating) AS gr 
                                        FROM ins_rated 
                                        GROUP BY charGuild 
                                        HAVING COUNT(*) >= 10
                                        ORDER BY gr DESC
                                        LIMIT 40");

列出了数据库中至少有 10 人的顶级团队。现在添加一种方法让前 15 名球员只计算平均球队得分是我迷路的地方。

Answer 1

SELECT charGuild, AVG(charRating) as gr FROM ins_rated GROUP BY charGuild order by gr desc limit 2