mysql - 仅提供用户ID即可选择固定范围的高分的有效方法

我有一张桌子，里面有每一个球员所打的每一场比赛的记录。我已经从这个问题的最上面的答案得到了一个很好的开头：Ranking with millions of entries，并且目前正在使用这个查询来检索每个玩家的最高得分和他们的等级：

SET @rank=0;

SELECT user_id, score, rank FROM (
    SELECT *, @rank := @rank + 1 as rank
    FROM high_scores
    JOIN (
        SELECT user_id as user_id2, MAX(score) AS max_score
        FROM high_scores
        GROUP BY user_id2
        ORDER BY max_score DESC) AS max_score_table
    ON max_score = high_scores.score AND high_scores.user_id = user_id2) derived_table
ORDER BY rank;

再次，这给了我一个很好的顺序列表，列出了每个玩家的最高得分和排名；但是，我也希望能够提供一个特定的user_id并将结果过滤到该用户的得分以及周围的x个较高和较低得分。
我想我需要对“cc>”的“派生表”执行一个SELECT ... WHERE，并使用返回行的user id来过滤顶级'rank'语句，但是除了查询甚至没有被接受（“派生的表不存在”），我这样做都需要我重新查询派生表（两次大于或小于测试），使查询的效率比它可能应该的更低。
如何筛选高分的完整列表并将其排序为单个SELECT条目和x个周围条目？对于我正在尝试的代码（或者我目前拥有的代码）的任何见解都将非常感谢。

最佳答案

SELECT ranks.rank, ranks.user_id, ranks.maxscore FROM (

  -- find the rank of each user by counting the number of
  -- users who have a score greater than that user's highest
  SELECT   currentuser.user_id,
           currentuser.maxscore,
           IFNULL(COUNT(DISTINCT high_scores.user_id),0) rank
  FROM     high_scores JOIN (
    -- current user's highest score
    SELECT user_id, MAX(score) maxscore FROM high_scores GROUP BY user_id
  ) currentuser ON high_scores.score > currentuser.maxscore
  GROUP BY currentuser.user_id

) ranks JOIN (

  -- find the rank of the user we want similarly
  SELECT IFNULL(COUNT(DISTINCT user_id),0) rank
  FROM   high_scores JOIN (
    -- our user's highest score
    SELECT MAX(score) maxscore FROM high_scores WHERE user_id = ?
  ) userscore ON high_scores.score > userscore.maxscore

-- filter for only those who are within given range from our user's rank
) userrank ON ranks.rank BETWEEN userrank.rank - ? AND userrank.rank + ?

-- sort the results
ORDER BY ranks.rank