MySQL排行榜表

Question

我正试图弄清楚如何根据WHERE子句从MySQL表中选择特定数量的行。我有一个有10个虚拟用户的表，我想得到特定用户的2个前一个和2个下一个用户及其排名。

user_id  |  points
==================
10        200
4        130
2        540
13        230
15        900
11        300
3        600
17        110
20        140
1        430
5        800

我实现了添加一个排名栏，如：

user_id  |  points  |  rank
===========================
15        900        1
5        800        2
3        600        3
2        540        4
1        430        5
11        300        6
13        230        7
10        200        8
20        140        9
4        130       10
17        110       11

但问题是我只想要5排。假设我正在用user_id = 11为用户检索数据。输出应该是这样的：

user_id  |  points  |  rank
===========================
2        540        4
1        430        5
11        300        6
13        230        7
10        200        8

其中CCD_ 2在中心，上面有2行，下面有2行。我尝试过嵌套UNIONS和SELECT语句，但似乎没有什么能正常工作。

Answer 1

如果您使用MySQL 8+，这里有一个建议：

WITH cte AS (
SELECT user_id, points,
ROW_NUMBER() OVER (ORDER BY points DESC) AS Rnk
FROM mytable)
SELECT cte2.user_id,
cte2.points,
cte2.Rnk
FROM cte cte1 
JOIN cte cte2
ON cte1.user_id=11
AND cte2.Rnk >= cte1.Rnk-2
AND cte2.Rnk <= cte1.Rnk+2

然后使用公共表表达式(cte(以user_id=11为条件进行自联接，得到-2和+2的Rnk值。

演示小提琴

由于您使用的是旧版本的MySQL，以下是我可以建议的：

SET @uid := 11;
SET @Rnk := (SELECT Rnk 
FROM
(SELECT user_id, points,
@r := @r+1 AS Rnk
FROM mytable 
CROSS JOIN (SELECT @r := 0) r
ORDER BY points DESC) v
WHERE user_id = @uid);

SELECT user_id, points, Rnk
FROM
(SELECT user_id, points,
@r := @r+1 AS Rnk
FROM mytable 
CROSS JOIN (SELECT @r := 0) r
ORDER BY points DESC) v
WHERE Rnk >= @Rnk-2
AND Rnk <= @Rnk+2;

如果您只使用user_id作为基础，那么这里唯一需要更改的部分就是SET @uid。剩下的查询刚好满足您的条件，即获得根据user_id检索到的排名之上和之下的两个职位。SET @Rnk中的基本查询与上一个的基本查询相同。其思想是将user_id=11的Rnk位置分配给@Rnk变量，然后在WHERE条件中使用它进行最后一次查询。

我不知道是否有任何在线fiddle仍在使用MySQL 5.1，但这里可能是最接近它的版本，MySQL 5.5演示fiddle。