如何只汇总此查询的3个最佳分数？ [英] How do I sum only the 3 best scores from this query?

问题描述

我只需要总结11个事件中每个球员的5个最佳成绩。下面的查询创建了一个排行榜，该排行榜将所有分数相加，但是我只能将5个最佳分数相加。

I need to sum only the 5 best scores for each player from 11 events. The query below creates a leaderboard which sums all the scores but I'm stuck as to how i can only sum the 5 best scores.

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

I've abbreviated the query to show only 4 events and getting best 3 results to shorten the post, but hope it gets across, what I need.

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;

表：championLeaderboard

table: championshipLeaderboard

+--------+---------+--------+
| 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 |
+--------+--------+--------+--------+--------+--------------+

推荐答案

MySQL 5.7

MySQL 5.7

实时测试： https://www.db-fiddle.com/f/4ufuFAXKf7mi5yefNQqoXM/15

select
    t.player, 
    sum(case when t.eventid = 1 then t.points end) as event1,
    sum(case when t.eventid = 2 then t.points end) as event2,
    sum(case when t.eventid = 3 then t.points end) as event3,
    sum(case when t.eventid = 4 then t.points end) as event4,

    sum(
        case when t.points >= (
            select best3.points 
            from tbl best3 
            where best3.player = t.player
            order by best3.points desc 
            limit 1 offset 2 -- offset starts with 0. so 2 is the third
        ) then 
            t.points
        end
    ) as best3            
from tbl t
group by t.player;

输出：

| player | event1 | event2 | event3 | event4 | best3 |
| ------ | ------ | ------ | ------ | ------ | ----- |
| 1      | 25     | 15     | 20     | 20     | 65    |
| 2      | 20     | 13     | 12     | 10     | 45    |

这篇关于如何只汇总此查询的3个最佳分数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！