ranking - MySQL高级等级查询

  显示原文与译文双语对照的内容
0 0

我有一个类似这样的表格:


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 

显然,我有多个不同映射的uid 。 我怎么会发现每个用户的排名都超出了总排名? 我知道我可以用 COUNT(DISTINCT map) 找到地图的总秩。 但是,我在选择特定用户及其在地图中排名时遇到 Having 问题。 任何帮助都是值得感激的 !

编辑:选择 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

时间: 原作者:

0 0

使用以下查询:-


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 |
+--------+------+------+-------+----------+

原作者:
...