MySQL组行 [英] Mysql group rows

问题描述

我有一张桌子，上面有: ID(车手ID) 时间(他完成的时间) 会话(完成时间的会话数)

I have a table with: Id (id of rider) Time (is the time that he has done) Session (number session where the time was done)

idlap   idRider session time
1        45652    1      4
2        54645    1      2
3        45652    2      2
4        54582    2      1
5        51284    1      3
6        54582    1      3
7        54645    2      4
8        51284    2      5
9        54582    2      2

我想知道如何在mysql中查询如下结果:

I wonder how to query in mysql a result like:

idRider   |fast sesion   |  count laps
45652     |     2        |     1
54645     |     1        |     1
51284     |     3        |     1
54582     |     2        |     2

查询应该在最短时间返回骑手获得最佳时间(快速运动)并计算圈数(在快速运动中完成) 由idRider分组. 谢谢.

The query should return in wich session the rider got the best time(fast session) and count the number of laps (done in the fast session) grouped by idRider. Thank you.

推荐答案

此查询将为您提供所需的结果.它将laps表加入到每个会话的每个骑手的最小时间和圈数，然后加入到每个骑手的最短时间以获取最快的会话:

This query will give you the results that you want. It JOINs the laps table to the minimum time and number of laps per rider per session, and then to the minimum time per rider to get the fastest session:

SELECT l.idRider, l.session, l.time, s.numlaps
FROM laps l
JOIN (SELECT idRider, `session`, MIN(time) AS fastest, COUNT(*) AS numlaps
      FROM laps
      GROUP BY idRider, `session`) s ON s.idRider = l.idRider AND s.session = l.session AND s.fastest = l.time
JOIN (SELECT idRider, MIN(time) AS fastest
      FROM laps
      GROUP BY idRider) f ON f.idRider = s.idRider AND f.fastest = s.fastest
ORDER BY idRider

输出:

idRider session time    numlaps
45652   2       2       1
51284   1       3       1
54582   2       1       2
54645   1       2       1

在dbfiddle上进行演示

这篇关于MySQL组行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！