mysql - SQL排名查询

Question

我从MySQL update statement to list ranking positions找到了一个排名查询，但我还有另一个要求，在找到排名后，我需要列出给定的player_id的排名以及给定的player_id之上和之下的少数玩家。

是否可以在单个查询中完成此操作？

示例数据:

rank player_id score
"1" "801"   "4995599"
"2" "800"   "4995599"
"3" "789"   "4995439"
"4" "1528"  "4993900"
"5" "1150"  "4993700"
"6" "2269"  "4993611"
"7" "465"   "4993523"

期望的输出:玩家 ID = 1528

rank player_id score
"2" "800"   "4995599"
"3" "789"   "4995439"
"4" "1528"  "4993900"
"5" "1150"  "4993700"
"6" "2269"  "4993611"

编辑1:示例代码:

SET @rank = 1, @seq = 1, @last = null;

select * from (
select *, @myRank := rank from (
SELECT *, @rank := if(@last = score, @rank, @seq) AS rank, @seq := @seq + 1, 
@last := score FROM score ORDER BY score DESC) as d where player_id = 789) 
as f where rank between @myRank -2 and @myRank + 2 ;

问候，库马尔·KS。

Answer 1

你的意思是这个吗

SET @r=0;
select * from (select @rownum:=@rownum+1 ‘rank’, p.* 
from player p,(SELECT @rownum:=0) r order by score desc) where rank>=@r-2 and rank<=@+2;

还是这个，我确信它的性能不能低

    SET @play_id = 0;
    set @above=2;
    set @below=2
    SELECT
        *
    FROM
        (
            SELECT
                @rownum :=@rownum + 1 rank,
                p.*
            FROM
                player p,
                (SELECT @rownum := 0) r
            ORDER BY
                score DESC
    ) t
WHERE
    rank >=(select rank from (
        SELECT
            @rownum :=@rownum + 1 ‘rank’,
            p.*
        FROM
            player p,
            (SELECT @rownum := 0) r
        ORDER BY
            score DESC
    ) a where play_id=@play_id) - @above
AND rank <=(select rank from (
        SELECT
            @rownum :=@rownum + 1 ‘rank’,
            p.*
        FROM
            player p,
            (SELECT @rownum := 0) r
        ORDER BY
            score DESC
    ) a where play_id=@play_id) + @above;