mysql - MySQL - 选择排名前 5 名

Question

我试图让用户排名在每个谱面图中获得他的最高表现。

我在每个beatmap中获得用户最高性能（仅取前5个性能）并将它们加在一起，但是当重复一个beatmap中的最高性能时它会失败......因为它计数了两次

我基于此解决方案，但对我来说效果不佳...

使用 MySQL 5.7

我做错了什么？

小提琴

使用此代码：

SET group_concat_max_len := 1000000;

SELECT @i:=@i+1 rank, x.userID, x.totalperformance FROM (SELECT r.userID, SUM(r.performance) as totalperformance 
FROM 
(SELECT Rankings.*
FROM   Rankings INNER JOIN (
  SELECT   userID, GROUP_CONCAT(performance ORDER BY performance DESC) grouped_performance
  FROM     Rankings
  GROUP BY userID) group_max
  ON Rankings.userID = group_max.userID
     AND FIND_IN_SET(performance, grouped_performance) <= 5

ORDER BY
  Rankings.userID, Rankings.performance DESC) as r
  GROUP BY userID) x
  JOIN 
     (SELECT @i:=0) vars
 ORDER BY x.totalperformance DESC

预期结果：

+------+--------+------------------+
| rank | userID | totalperformance |
+------+--------+------------------+
| 1    | 1      | 450              |
+------+--------+------------------+
| 2    | 2      | 250              |
+------+--------+------------------+
| 3    | 5      | 140              |
+------+--------+------------------+
| 4    | 3      | 50               |
+------+--------+------------------+
| 5    | 75     | 10               | 
+------+--------+------------------+
| 6    | 45     | 0                | --
+------+--------+------------------+
| 7    | 70     | 0                | ----> This order is not relevant
+------+--------+------------------+
| 8    | 76     | 0                | --
+------+--------+------------------+

实际结果：

+------+--------+------------------+
| rank | userID | totalperformance |
+------+--------+------------------+
| 1    | 1      | 520              |
+------+--------+------------------+
| 2    | 2      | 350              |
+------+--------+------------------+
| 3    | 5      | 220              |
+------+--------+------------------+
| 4    | 3      | 100              |
+------+--------+------------------+
| 5    | 75     | 10               |
+------+--------+------------------+
| 6    | 45     | 0                | --
+------+--------+------------------+
| 7    | 70     | 0                | ----> This order is not relevant
+------+--------+------------------+
| 8    | 76     | 0                | --
+------+--------+------------------+

标签： mysqlrankingmysql-5.7

Answer 1

正如您所提到的，您只选择每个用户的前 5 名性能，beatmaps那么您可以尝试这种方式：

select @i:=@i+1, userid,performance from (
select userid,sum(performance) as performance from (
select 
  @row_number := CASE WHEN @last_category <> t1.userID THEN 1 ELSE @row_number + 1 END AS row_number,
  @last_category :=t1.userid,
t1.userid,
t1.beatmapid,
t1.performance

from (
select 
 userid, beatmapid,
 max(performance) as performance
from Rankings 
group by userid, beatmapid
) t1 
CROSS JOIN (SELECT @row_number := 0, @last_category := null) t2
ORDER BY t1.userID , t1.performance desc
) t3
where row_number<=5
group by userid
)
t4 join  (SELECT @i := 0 ) t5
order by performance desc

上面的查询不会考虑重复的性能分数，只选择前 5 个性能值。

演示