列出每位玩家前5名的平均值

After reading several other answers of similar problems, I still can't wrap my head to achieve the following:

在阅读了几个类似问题的其他答案后，我仍然无法实现以下目标：

Having a list of player scores, I would like to get the top n scores of each player (the scores table only has the player id and a value). The final purpose is to aggregate the scores with the AVG() function.

有一个球员得分列表，我想得到每个球员的前n个得分（得分表只有玩家ID和值）。最终目的是使用AVG（）函数聚合分数。

Also note that the n bound is just a limit; a player may have less than n scores, in which case all of them should be computed.

另请注意，n界限只是一个限制;玩家可能少于n分，在这种情况下，应该计算所有分数。

Once the results are calculated, joining with the player table will allow to expand each player id into printable information.

计算结果后，加入播放器表将允许将每个玩家ID扩展为可打印信息。

3 个解决方案

#1

Start from here:

从这里开始：

drop table if exists scores;
create table scores (playerid integer, score integer);
insert into scores values 
(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),
(2,1),(2,2),(2,3),(2,4);

select p1.playerid, p1.score
from scores p1, scores p2
where p1.playerid = p2.playerid
and 
p1.score >=
ifnull((select score 
 from scores 
 where playerid=p1.playerid 
 order by score desc limit 4,1
),0)
group by p1.playerid,p1.score;

which will give you the desired list of top scores.

这将为您提供所需的最高分。

#2

In MySQL you need vars to accomplish yours requirements:

在MySQL中，您需要vars来满足您的要求：

select
    idplayer, Score
from
(
    select
        idplayer, T.Score,
        @r := IF(@g=idplayer,@r+1,1) RowNum,
        @g := idplayer
    from (select @g:=null) initvars
    CROSS JOIN 
    (
        SELECT s.Score, 
               s.idplayer
        FROM scores s
        ORDER BY idplayer, s.score DESC
    ) T
) U
WHERE RowNum <= 3

Test it at sqlfiddle:

在sqlfiddle测试：

create table scores( idplayer int, score int);
insert into scores values 
   (1,5),     (1,7),      (1,18),      (1,27),      (2,6);

Results:

结果：

| IDPLAYER | SCORE |
--------------------
|        1 |    27 |
|        1 |    18 |
|        1 |     7 |
|        2 |     6 |

#3

I'm not 100% sure this will work in mysql. However, the following captures the idea as a correlated subquery:

我不是100％肯定这将在mysql中工作。但是，以下内容将该想法捕获为相关子查询：

select p.*,
       (select sum(score)
        from (select score
              from scores s
              where s.playerid = p.playerid
              order by score desc
              limit 5
             ) s2
       ) as summax5
from players p

#1