Mysql row_number 根据值排名

我试图根据数据库中的值找出某人的排名。但我不明白row_number() here.

基本查询，我想添加排名：

select player_id,value from player_storage where `key` = 40001 order by value desc;
+-----------+-------+
| player_id | value |
+-----------+-------+
|        10 |   333 |
|        11 |    31 |
|        15 |    12 |
|         9 |     3 |
|         1 |     0 |
|         8 |     0 |
|        12 |     0 |
|        13 |     0 |
|        14 |     0 |
+-----------+-------+

可以说我是player_id = 11，我想知道我在排名中的位置。

尝试了几种解决方案，例如 row_number() over (partition by value)，但计算的排名总是错误的。例子：

select player_id,value,row_number() over (partition by value order by value desc) as rank from player_storage where `key` = 40001 order by rank;
+-----------+-------+------+
| player_id | value | rank |
+-----------+-------+------+
|         1 |     0 |    1 |
|         9 |     3 |    1 |
|        10 |   333 |    1 |
|        11 |    31 |    1 |
|        15 |    12 |    1 |
|         8 |     0 |    2 |
|        12 |     0 |    3 |
|        13 |     0 |    4 |
|        14 |     0 |    5 |
+-----------+-------+------+

Or...

set @rank = 0 ; select rank,player_id,value from (select player_id,value,(@rank:=@rank+1) as rank from player_storage, (select @rank := 0) r where `key` = 40001 order by value desc) t order by rank;
Query OK, 0 rows affected (0.000 sec)

+------+-----------+-------+
| rank | player_id | value |
+------+-----------+-------+
|    1 |         1 |     0 |
|    2 |         8 |     0 |
|    3 |         9 |     3 |
|    4 |        10 |   333 |
|    5 |        11 |    31 |
|    6 |        12 |     0 |
|    7 |        13 |     0 |
|    8 |        14 |     0 |
|    9 |        15 |    12 |
+------+-----------+-------+

排名窗口函数似乎是显而易见的答案，顺便说一句，分区依据是可选的

create table t
(player_id int, value int);
insert into t values
(        10 ,   333 ),
(        11 ,    31 ),
(        15 ,    12 ),
(         9 ,     3 ),
(         1 ,     0 ),
(         8 ,     0 ),
(        12 ,     0 ),
(        13 ,     0 ),
(        14 ,     0 );

select *,
        rank() over (order by value desc)
from t

+-----------+-------+-----------------------------------+
| player_id | value | rank() over (order by value desc) |
+-----------+-------+-----------------------------------+
|        10 |   333 |                                 1 |
|        11 |    31 |                                 2 |
|        15 |    12 |                                 3 |
|         9 |     3 |                                 4 |
|        13 |     0 |                                 5 |
|        14 |     0 |                                 5 |
|         1 |     0 |                                 5 |
|         8 |     0 |                                 5 |
|        12 |     0 |                                 5 |
+-----------+-------+-----------------------------------+
9 rows in set (0.001 sec)

如果你只想要 11，请将代码放入 cte

with cte as
(select *,
        rank() over (order by value desc) rnk
from t
)
select player_id, rnk
from cte 
where player_id = 11;