MySQL 在动态生成的排行榜中选择用户的排名

Question

我有一个用户预测表，用于生成顶级用户及其排名的排行榜。

表格(预测)如下所示:

  id  |  userid  |  fixtureid   |  correct
  1   |    1     |      1       |     1
  2   |    2     |      5       |     0
  3   |    2     |      6       |     1
  4   |    2     |      7       |     1
  5   |    3     |      6       |     0

目前我使用此查询来生成排行榜，然后循环结果:

SELECT userid, (correct * 3) AS score 
FROM predictions 
GROUP BY userid 
ORDER BY score DESC

然后，这将显示与此类似的排行榜，排名是在每行后面使用 $i++; 在 php 中计算的:

User Id  |  Score  |
    2    |    6    | #Rank is 1
    1    |    3    | #Rank is 2
    3    |    0    | #Rank is 3

我想要实现的目标

我希望能够找出用户在排行榜中的排名，而无需构建整个表格，只需使用他们的 userid。

考虑到用户将来可能会增加到大量，我该如何做到这一点？

我想要实现的目标的伪代码:

SELECT rank 
FROM predictions
WHERE userid = 3

这将返回:

User Id  |  Score  |
    3    |    0    | #Rank is 3

Answer 1

我不明白你是如何生成“位置”列的，但无论如何，您是否探索过使用 View 的可能性？

您只需运行此命令一次:

CREATE VIEW `leaderboard` AS
SELECT 
    userId, 
    (correct * 3) AS score 
FROM predictions 
GROUP BY userid 
ORDER BY score DESC

然后您可以将创建的 View 视为应用程序中的普通表。

以下是有关如何使用它的示例:

SELECT 
    (SELECT COUNT(*) FROM leaderboard WHERE score >= L.score) AS position,
    L.userid,
    L.score
FROM leaderboard L
WHERE L.userid = 3