mysql - 分组，计数和连续圈数

我不能解决这个问题（比赛），我需要显示每圈的时间。这是时刻表

 id    | race_id     | car_num | time       |
 +-----+-------------+---------+------------+
 1     | 8           | 25      | 00:09:05   |
 2     | 8           | 33      | 00:09:35   |
 3     | 8           | 10      | 00:09:55   |
 4     | 8           | 25      | 00:18:15   |
 5     | 8           | 33      | 00:19:05   |
 6     | 8           | 25      | 00:39:45   |

我试过这个问题：

SELECT
    car_num, COUNT(car_num) as laps, race_id, concat(vlap,'-',time) as times
FROM
    (SELECT num_car, concat(time,'-',v1) vlap
     FROM tiempos) vti
GROUP BY
    car_num

这是所需的输出：

 car_num    | laps     | race_id | times                                        |
 +-----+-------------+---------+------------------------------------------------+
 25         | 3        | 8       | lap1 00:09:05, lap2 00:18:15, lap3 00:39:45  |
 33         | 2        | 8       | lap1 00:09:35, lap2 00:19:05                 |
 10         | 1        | 8       | lap1 00:09:55                                |

我头晕，有什么主意

最佳答案

你可以通过一个简单的group_concat()，得到你想要的大部分：

select car_num, count(*) as laps, race_id, group_concat(time order by id separator ', ' ) as times
from tiempos t
group by car_num, race_id;

如果你需要圈数，你可以通过变量得到：

select car_num, count(*) as laps, race_id, group_concat('lap', rn, ' ', time order by id separator ', ' ) as times
from (select t.*,
             (@rn := if(@t = time, @rn + 1,
                        if(@t := time, 1, 1)
                       ) as rn
      from tiempos t cross join
           (select @rn := 0, @t := '') vars
      order by race_id, car_num, time
     ) t
group by car_num, race_id;

关于mysql - 分组，计数和连续圈数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25985915/