php - MYSQL 获取行排名

Question

我有一个 mysql 表，我需要获取随机行并获取总 View 的排名

+--------+------------+---------+
| id     | name       |totalview|
+--------+------------+---------+
| 1      | ex1        |   20    |
| 2      | ex2        |   100   |
| 3      | ex3        |   30    |
| 4      | ex4        |   40    |
+--------+------------+---------+

例如:

SELECT * FROM `table` WHERE `id` = '$rand';

$rand 可能是 1 或 2 等..

我需要通过总 View 获得该行的排名

谢谢

Answer 1

SELECT *,
       (SELECT COUNT(*) FROM table t2 WHERE totalview > t1.totalview ) + 1 cnt
  FROM table t1
 WHERE id = '$rand';