如何在mysql中创建排行榜排名

Question

我有一个数据列表，我需要根据他们的得分显示他们的排名，获得前1-3名或前10名很容易，但我可以根据他们在行中的位置获得他们的排名吗？例如，如果你在这一行的中间，我会试图得到前两个和后两个。

用户表

ID| User_id|Score|
1     1142    12
2     53      55
3     12      44
4     66      33
5     77      22
6     123     11
7     321     11
8     456     88
9     654     66
10    896     44
11    536     24
12    990     11

因此，例如user_id: 123将登录，我应该能够检索数据

ID| User_id|Score|
4     66      33
5     77      22
6     123     11
7     321     11
8     456     88

，我正试图在我的视图中显示这个

像这样。所以这将是视图中的输出。

RANK 21:  1142
RANK 22:   77
RANK 23: 123
RANK 24:  N/A
RANK 25:  N/A

如果没有低于或高于他，则应该是N/A，想法是登录的用户应该总是在中间。

我怎样才能拿到前两个和后两个？这在MYSQL中可能吗？如果没有低于或高于他的人，我该怎么办。任何帮助都将不胜感激。

Answer 1

您可以尝试以下操作：

select * from  users where User_id=123
union all (
select * from users 
where User_id <  123
limit 2
) 
union all (
select * from users 
where User_id > 123 limit 2
) 
order by User_id

Answer 2

SELECT ID,user_id,score
FROM users
WHERE user_id =123 OR user_id BETWEEN (LAG(LAG(user_id=123)) AND LEAD(LEAD(user_id=123)))
ORDER BY ID          

Answer 3

您可以说123的相对秩为0，而我们感兴趣的其他行的相对秩是-2，-1，1,2。一旦我们有了123的实际排名，那么其他行的实际排名就很容易计算了。

select t.rnk + relativerank as finalrank,
coalesce(dummy,'n/a') finaluserid  
from
(
select 0 as relativerank,t.*, 123 as dummy from t where user_id = 123
union all
select -1 ,t.*,(select user_id from t t1 where t1.Score > t.score order by t1.score asc  limit 0,1) plus1  from t where user_id = 123
union all
select -2, t.*,(select user_id from t t1 where t1.Score > t.score order by t1.score asc  limit 1,1) plus2  from t where user_id = 123
union all
select 1, t.*,(select user_id from t t1 where t1.Score < t.score order by t1.score desc limit 0,1) minus1 from t where user_id = 123
union all
select 2, t.*,(select user_id from t t1 where t1.Score < t.score order by t1.score desc limit 1,1) minus2 from t where user_id = 123
) s
left join
(select  t.*, 
if(score <> @p,@rnk:=@rnk+1,@rnk:=@rnk) rnk,
@p:=score p
from t
cross join (select @rnk:=0,@p:=0) r
order by score desc
) t
on t.user_id = s.user_id
order by finalrank ;
+-----------+-------------+
| finalrank | finaluserid |
+-----------+-------------+
|         7 | 77          |
|         8 | 1142        |
|         9 | 123         |
|        10 | n/a         |
|        11 | n/a         |
+-----------+-------------+
5 rows in set (0.05 sec)