mysql - 如何仅对该查询中的 3 个最佳分数求和？

Question

我只需要对 11 场比赛中每位选手的 5 个最佳成绩求和。下面的查询创建了一个排行榜，它总结了所有的分数，但我对如何只能总结 5 个最好的分数感到困惑。

我已将查询缩写为仅显示 4 个事件并获得最佳 3 个结果以缩短帖子，但希望它能传达我所需要的内容。

SELECT playerID AS Player,
  SUM(CASE WHEN championshipleaderboard.eventID = 1 THEN championshipleaderboard.points ELSE 0 END) AS Event1,
  SUM(CASE WHEN championshipleaderboard.eventID = 2 THEN championshipleaderboard.points ELSE 0 END) AS Event2,
  SUM(CASE WHEN championshipleaderboard.eventID = 3 THEN championshipleaderboard.points ELSE 0 END) AS Event3,
  SUM(CASE WHEN championshipleaderboard.eventID = 4 THEN championshipleaderboard.points ELSE 0 END) AS Event4,
  SUM(championshipleaderboard.points) AS Total 
FROM (
  championshipleaderboard JOIN members ON championshipleaderboard.playerId = members.playerId
)
GROUP BY championshipleaderboard.playerId
ORDER BY Total DESC;

表:冠军排行榜

+--------+---------+--------+
| Player | EventID | Points |
+--------+---------+--------+
|      1 |       1 |     25 |
|      2 |       1 |     20 |
|      1 |       2 |     15 |
|      2 |       2 |     13 |
|      1 |       3 |     20 |
|      2 |       3 |     12 |
|      1 |       4 |     20 |
|      2 |       4 |     10 |
+--------+---------+--------+

当前结果是

+--------+--------+--------+--------+--------+-------+
| Player | Event1 | Event2 | Event3 | Event4 | Total |
+--------+--------+--------+--------+--------+-------+
|      1 |     25 |     15 |     20 |     20 |    80 |
|      2 |     20 |     13 |     12 |     10 |    55 |
+--------+--------+--------+--------+--------+-------+

需要的结果是

+--------+--------+--------+--------+--------+--------------+
| Player | Event1 | Event2 | Event3 | Event4 | Total(best3) |
+--------+--------+--------+--------+--------+--------------+
|      1 |     25 |     15 |     20 |     20 |           65 |
|      2 |     20 |     13 |     12 |     10 |           45 |
+--------+--------+--------+--------+--------+--------------+

Answer 1

这是一种方法 - 尽管在 MySQL 8+ 中，您会使用更多现代技术...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(Player INT NOT NULL
,EventID INT NOT NULL
,Points INT NOT NULL
,PRIMARY KEY(player,eventid)
);

INSERT INTO my_table VALUES
(1,1,25),
(2,1,20),
(1,2,15),
(2,2,13),
(1,3,20),
(2,3,12),
(1,4,20),
(2,4,10);

SELECT player
     , SUM(points) top3 
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev = player THEN @i:=@i+1 ELSE @i:=1 END i
            , @prev:=player 
         FROM my_table x
            , (SELECT @prev:=null,@i:=0) vars ORDER BY player,points
DESC) a 
        WHERE i<=3 
        GROUP 
           BY player;
+--------+------+
| player | top3 |
+--------+------+
|      1 |   65 |
|      2 |   45 |
+--------+------+