mysql - 查找用户最低圈速的等级

我有很多赛车手，在不同的赛道上比赛。我想在每个特定的赛道上获得他们的最佳圈速，并将其与其他用户在该特定的赛道上的最佳圈速进行比较。我想为所有曲目的所有用户都这样做。所以我想返回一个大的排行榜。
基本上，如果我有10个赛车手，每个人都在赛道1完成了25圈，我只想确定每个赛车手的最佳圈速，并在每个赛道上对这些圈速进行排序。
我想我差不多有了，请看下面的查询和一个sqlfiddle链接：http://sqlfiddle.com/#!9/64d6c/2
为了确保你有所有的作品，还有一个“tracks.verified”栏。这将确定轨迹是否已验证。你真的不需要担心这个参数，如果你想的话可以忽略它。我指的是这句话：

AND s1.track_id IN (SELECT id FROM tracks WHERE verified=1)

http://sqlfiddle.com/#!9/64d6c/2

SELECT track_id, user_id, duration, @rank := @rank + 1 AS rank FROM
(
  SELECT DISTINCT s1.track_id, s1.user_id, s1.duration
  FROM session_lap_times s1
  LEFT JOIN session_lap_times s2
    ON s1.user_id=s2.user_id
   AND s1.track_id = s2.track_id
   AND s1.duration > s2.duration
  WHERE s2.duration IS NULL
    AND s1.track_id IN (SELECT id FROM tracks WHERE verified=1)
  ORDER BY s1.duration ASC
) zz, (SELECT @rank := 0) z;

这将输出：

+----------+---------+------------------+------+
| track_id | user_id | duration         | rank |
+----------+---------+------------------+------+
|       15 |       2 | 71001.5129995350 |    1 |
|       15 |       1 | 75001.5129995350 |    2 |
|       17 |       1 | 90258.1180334090 |    3 |
|       17 |       2 | 90897.0659971240 |    4 |
+----------+---------+------------------+------+

问题是它的排名都是靠对方，而不是靠每条赛道。这些结果应该如下所示：

+----------+---------+------------------+------+
| track_id | user_id | duration         | rank |
+----------+---------+------------------+------+
|       15 |       2 | 71001.5129995350 |    1 |
|       15 |       1 | 75001.5129995350 |    2 |
|       17 |       1 | 90258.1180334090 |    1 |
|       17 |       2 | 90897.0659971240 |    2 |
+----------+---------+------------------+------+

我认为这是问题所在：

AND s1.track_id IN (SELECT id FROM tracks WHERE verified=1)

因为如果我把s1.track_id=15（例如），这对一个单轨来说是正常的。只是不全是。
感谢您的帮助！
编辑：
我正在尝试按用户ID筛选结果。所以我将您的整个查询放入子查询中。有没有什么方法可以在不将其放入子查询的情况下执行此操作？谢谢

select track_id, user_id, duration, rank from (SELECT track_id, user_id,duration,
  CASE WHEN track_id!=@track THEN @rank:=0 END as reset,
  @rank := @rank + 1 AS rank,
  @track:=track_id
FROM
(
  SELECT DISTINCT s1.track_id, s1.user_id, s1.duration
  FROM session_lap_times s1
  LEFT JOIN session_lap_times s2
    ON s1.user_id=s2.user_id
   AND s1.track_id = s2.track_id
   AND s1.duration > s2.duration
  WHERE s2.duration IS NULL
    AND s1.track_id IN (SELECT id FROM tracks WHERE verified=1)
  ORDER BY s1.duration ASC
) zz, (SELECT @rank := 0, @track := null) z) as x where user_id=1

至于计算我得到的驱动程序查询总数：

SELECT track_id, COUNT(user_id) FROM (SELECT track_id,user_id FROM session_lap_times GROUP BY track_id,user_id) as z GROUP BY track_id

再说一次，没有子查询有更好的方法吗？谢谢！：）

最佳答案

SELECT track_id, user_id, duration,
  CASE WHEN track_id!=@track THEN @rank:=0 END as reset,
  @rank := @rank + 1 AS rank,
  @track:=track_id
FROM
(
  SELECT DISTINCT s1.track_id, s1.user_id, s1.duration
  FROM session_lap_times s1
  LEFT JOIN session_lap_times s2
    ON s1.user_id=s2.user_id
   AND s1.track_id = s2.track_id
   AND s1.duration > s2.duration
  WHERE s2.duration IS NULL
    AND s1.track_id IN (SELECT id FROM tracks WHERE verified=1)
  ORDER BY s1.duration ASC
) zz, (SELECT @rank := 0, @track := null) z;

用你的小提琴测试。
引入一个附加变量，并将rank重置为track_id更改