mysql - Ranking Where 子句

Question

我的查询有问题。我想从我的排名查询中选择数据。我的查询输出完美如:

------------------------------
 Rank | ID | Username | Value
-------------------------------
  1   | 5 | Julian    | 5000
  2   | 2 | Masha     | 2400
  3   | 4 | Misha     | 2300
  4   | 1 | Jackson   | 1900
  5   | 9 | Beruang   | 400
-------------------------------

但是当我选择 ID = 4 时，输出如下:

------------------------------
 Rank | ID | Username | Value
-------------------------------
  ***1***   | 4 | Misha     | 2300
-------------------------------

排名的输出是1，不是3。

我的查询是:

 SELECT @curRank := @curRank + 1 AS rank,
       a.id, a.username
FROM  partimer a CROSS JOIN
      (SELECT @curRank := 0) vars
# WHERE  a.id = 4
ORDER By id;

Answer 1

如果 Rank 是动态计算的，您可以这样做:

SELECT *
FROM (
  SELECT @curRank := @curRank + 1 AS rank
       , a.id
       , a.username
  FROM partimer a
  CROSS JOIN (SELECT @curRank := 0) vars
  ORDER BY value
) p
WHERE p.id = 4;

这样，您可以存储带有排名的临时表，然后从中进行选择。