时间戳

首页 > 解决方案 > 如何仅汇总此查询中的 3 个最佳分数？

mysql - 如何仅汇总此查询中的 3 个最佳分数？

问题描述

我只需要总结 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 |
+--------+--------+--------+--------+--------+--------------+

标签： mysqlgroup-byaggregate-functionsgreatest-n-per-groupmysql-5.7

解决方案

您可以使用相关子查询来查找第三个最佳分数，并将等于/大于第三个最佳分数的分数相加：

SELECT Player
     , MIN(CASE WHEN Eventid = 1 THEN points END) AS Event1
     , MIN(CASE WHEN Eventid = 2 THEN points END) AS Event2
     , MIN(CASE WHEN Eventid = 3 THEN points END) AS Event3
     , MIN(CASE WHEN Eventid = 4 THEN points END) AS Event4
     , SUM(CASE WHEN (Points, ID) >= (
        SELECT Points, ID
        FROM t AS x
        WHERE x.Player = t.Player
        ORDER BY Points DESC, ID DESC
        LIMIT 2, 1
     ) THEN Points END) AS TopThree
FROM t
GROUP BY Player

请注意，排序点之间的联系被Points DESC, ID DESC;排序打破。因此，如果玩家的分数(1, 20), (2, 20), (3, 30), (4, 30)被(4, 30), (3, 30), (2, 20)相加。

DB<>Fiddle 上的演示

推荐阅读