MySQL 高级排名查询

Question

我有一个看起来像这样的表:

map         uid     time    name
'first'     1       5.0     'Jon'
'first'     3       4.9     'Robin'
'second'    1       2.0     'Jon'
'first'     2       5.3     'Max'
'second'    3       2.1     'Robin'

我目前正在使用这个选择值:

SELECT records.* FROM `records` WHERE `uid` = '3' ORDER BY `records`.`time` ASC 

很明显，我有多个用于不同 map 的 uid。我如何从总排名中找到每个用户的排名？我知道我可以使用 COUNT(DISTINCT map) 找到 map 的总排名。但是，我在选择特定用户及其在 map 中的排名时遇到问题。任何帮助将不胜感激!

编辑:选择 uid 3 时所需的输出如下:

map         uid     time    name        position    totalposition (totalposition would be COUNT(DISTINCT map))
'first'     3       4.9     'Robin'     2           3
'second'    3       2.1     'Robin'     2           2

Answer 1

使用以下查询:-

mysql> set @pos = 0; select records.*, @pos:=@pos+1 as position from records order by time desc;

输出:

+--------+------+------+-------+--------------+
| map    | uid  | time | name  | position     |
+--------+------+------+-------+--------------+
| first  |    2 | 5.30 | Max   |            1 |
| first  |    1 | 5.00 | jon   |            2 |
| first  |    3 | 4.90 | Robin |            3 |
| second |    3 | 2.10 | Robin |            4 |
| second |    1 | 2.00 | Jon   |            5 |
+--------+------+------+-------+--------------+

现在，接收特定的位置:

mysql> set @pos = 0; select * from (select records.*, @pos:=@pos+1 as position
mysql> from records order by time desc) as t where uid = 3;

输出:

+--------+------+------+-------+----------+
| map    | uid  | time | name  | position |
+--------+------+------+-------+----------+
| first  |    3 | 4.90 | Robin |        3 |
| second |    3 | 2.10 | Robin |        4 |
+--------+------+------+-------+----------+