我正在嘗試根據資料庫中的值找出某人的排名。但我在這里想不通row_number()。
基本查詢，我想添加排名：
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（按值磁區），但計算出的排名總是錯誤的。例子：
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 |
----------- ------- ------
要么...
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 |
------ ----------- -------
uj5u.com熱心網友回復：
排名視窗函式似乎是顯而易見的答案，順便說一句，partition by 是可選的
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/430032.html
標籤：mysql 选择 sql-order-by 行号