如何在 MySQL 中选择 TOP n 分组值的平均值？

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 

Answer 2

错误代码：1235 此版本的 MySQL 尚不支持 'LIMIT & IN/ALL/ANY/SOME 子查询'

我认为您可能需要使用一些编码。

我能做的最好的事情是：

SELECT charGuild, charRating
FROM ins_rated a
WHERE charRating = (SELECT MAX(charRating) FROM ins_rated b WHERE b.charGuild = a.charGuild)
OR charRating = (SELECT MAX(charRating) FROM ins_rated b WHERE b.charGuild = a.charGuild
    AND charRating < (SELECT MAX(charRating) FROM ins_rated c WHERE c.charGuild = a.charGuild));

输出：

charGuild     charRating   
team 1        100          
team 1        90           
team 2        100          
team 2        95           

编辑：从头开始。它获得了 2 个最高的独特分数。如果队伍中有 2 名玩家获得 100 分，则不会选择（100,100）作为最高分，而是选择（100,95）。

Answer 3

呜呼！

SELECT charGuild, AVG(charRating) average
FROM (SELECT charRating, charGuild FROM ins_rated a ORDER BY charRating DESC LIMIT 4) top4
GROUP BY charGuild

输出：

charGuild           average  
team 1              95.0000  
team 2              97.5000  

编辑：从头开始。此假设假设团队数量已知，并且每个团队至少有 2 人。

Answer 4

我喜欢这个：
它没有我其他两个答案的任何缺陷......

SELECT charGuild, AVG(charRating) average
FROM (
    SELECT
        charRating,
        charGuild,
        @num := IF(@guild = charGuild, @num + 1, 1) AS row_number,
        @guild := charGuild
    FROM ins_rated a
        ORDER BY charGuild DESC
) ordered
WHERE row_number <= 2
GROUP BY charGuild;

输出：

charGuild     average
team 1        95.0000         
team 2        97.5000