mysql - PHP MYSQL - 基于多行获取单个用户的排名(位置)

Question

使用以下 MySQL 表:

+---------+-----------+
| user_id | user_name |
+---------+-----------+
|    1    |   user 1  |
|    2    |   user 2  |
|    3    |   user 3  |
|    4    |   user 4  |
|    5    |   user 5  |
+---------+-----------+
+---------+-------------+
| user_id | user_staff  |
+---------+-------------+
|    1    |  xxxxxx     |
|    1    |  xxxxxx     |
|    2    |  xxxxxx     |
|    2    |  xxxxxx     |
|    2    |  xxxxxx     |
|    3    |  xxxxxx     |
|    3    |  xxxxxx     |
|    4    |  xxxxxx     |
+---------+-------------+

此查询获取排名表:

SELECT usr.user_name, COUNT(stf.user_id) AS score 
  FROM table_stafs AS stf 
    LEFT JOIN table_users AS usr ON usr.user_id=stf.user_id 
  GROUP BY usr.id 
  ORDER BY score DESC

如何获取单个用户的位置？

Answer 1

如果需要排名，可以在查询结果中添加自动递增列，例如:

SELECT usr.user_id,COUNT(stf.*) AS score, @r := @r + 1 AS rank
FROM table_stafs AS stf, (SELECT @r := 0) a 
LEFT JOIN table_users AS usr ON usr.user_id=stf.user_id 
GROUP BY usr.id ORDER BY score DESC