按偏移量选择MySQL上的用户排名

Question

我正尝试在MySQL中执行一个查询，该查询仅返回表中的10个用户，但其排名值是xp列的排序结果。现在我有这样的想法：

SELECT id, xp, @curRank := @curRank + 1 AS rank
    FROM usuarios, (SELECT @curRank := 0) r
    ORDER BY xp DESC LIMIT 10;

在获取前10个用户时，它看起来工作得很好。

+--------------------+------+------+
| id                 | xp   | rank |
+--------------------+------+------+
| 373901344995803138 | 5863 |    1 |
| 701198768049225770 | 5692 |    2 |
| 239203656405221376 | 4961 |    3 |
| 692489002942726154 | 4508 |    4 |
| 416988898628206593 | 3669 |    5 |
| 312003290378534912 | 3155 |    6 |
| 608344569381126167 | 3059 |    7 |
| 671949142473310238 | 3041 |    8 |
| 549743978191519744 | 2991 |    9 |
| 592440479577145383 | 2519 |   10 |
+--------------------+------+------+

但是，当我尝试获取LIMIT 10,10来获取11到20之间的用户时，尽管它们是有序的，但它们的全局排名是不正确的，因为在偏移之前，@curRank并没有为所有用户增加。

+--------------------+------+------+
| id                 | xp   | rank |
+--------------------+------+------+
| 638196238436532234 | 1888 |    1 |
| 601269358349516833 | 1447 |    2 |
| 548357514497097743 | 1338 |    3 |
| 203591312031744000 | 1330 |    4 |
| 379034072519016469 | 1283 |    5 |
| 563804445654122497 | 1086 |    6 |
| 421296425981181952 | 1025 |    7 |
| 263816867100098560 |  850 |    8 |
| 631330775379214371 |  776 |    9 |
| 442529076511637504 |  702 |   10 |
+--------------------+------+------+

当使用LIMIT时，我不知道如何使全局排名起作用。

Answer 1

在MySQL 8.0中，只需使用窗口函数，正如戈登·利诺夫所演示的那样。

在早期版本中，您基本上需要一个子查询来执行您想要的操作。我会推荐：

SELECT *
FROM (
    SELECT id, xp, @curRank := @curRank + 1 AS rank
    FROM (SELECT * FROM usuarios ORDER BY xp DESC) u
    CROSS JOIN (SELECT @curRank := 0) r
    ORDER BY xp DESC
) t
ORDER BY xp DESC 
LIMIT 10, 10;

子查询首先对所有用户进行排序，然后可以安全地在外部查询中进行过滤。请注意，查询首先在子查询中按xp对表进行预排序:这更安全(在MySQL中，用户变量比较棘手)。

实际上，您甚至不需要在外部查询中使用LIMIT；您可以使用WHERE子句：

SELECT *
FROM (
    SELECT id, xp, @curRank := @curRank + 1 AS rank
    FROM (SELECT * FROM usuarios ORDER BY xp DESC) u
    CROSS JOIN (SELECT @curRank := 0) r
    ORDER BY xp DESC
) t
WHERE rank BETWEEN 11 AND 20
ORDER BY rank

Answer 2

相反，请使用row_number()

SELECT id, xp, row_number() over (order by cp desc) as rnk
FROM usuarios
ORDER BY xp DESC
LIMIT 10;