mysql - 在同一行中获取 MIN 和 MAX

Question

想象一个名为 training_route 的表，其中包含几行，每行都有一个 rowId、gpsLocation 和一个 athleteId 和考虑以下几点:

• 最低的 rowId 定义第一个位置，最大的 rowId 定义最后一个位置
• 所有行共同定义了相关运动员运行的路线

例如

rowId   athleteId   gpsLocation
100       1           "40.7127837,-74.00594130000002" <- first location for athlete #1
101       1           "41.1234872,-71.41300000022342"
102       1           "42.1234872,-69.23112200022342" <- last location for athlete #1
103       2           "39.5993499,-74.00594130000002" <- first location for athlete #2
104       2           "38.9093885,-73.31300000022342"
106       2           "37.1234872,-63.34215200022342" <- last location for athlete #2
107       3           ...

我想要的是按 athleteId 分组的每条路线的第一个和最后一个位置，在同一行中通过相同的查询:

athleteId   firstLocation                       lastLocation
1           "40.7127837,-74.00594130000002"     "42.1234872,-69.23112200022342"
2           "39.5993499,-74.00594130000002"     "37.1234872,-63.34215200022342"
3           ...             ...

那个 MySQL 查询会是什么样子？

附言

我试过这样的:

SELECT training_route.athleteId,
       ( SELECT training_route.gpsLocation FROM training_route WHERE training_route.athleteId = route.athleteId ORDER BY training_route.rowId ASC  LIMIT 1 ) AS firstLocation,
       ( SELECT training_route.gpsLocation FROM training_route WHERE training_route.athleteId = route.athleteId ORDER BY training_route.rowId DESC LIMIT 1 ) AS lastLocation,
FROM   training_route AS route
WHERE  training_route.athleteId IN ( 1, 2, 3 ) GROUP BY training_route.athleteId;

但我很惭愧，这是我迄今为止想出的最好的，因为它在性能方面是完全不能接受的。

Answer 1

以下应该适用于 mysql

SELECT *
FROM   (
        SELECT MIN(rowid) minrowid
               , MAX(rowid) maxrowid
               , athleteid
        FROM  training_route
        GROUP BY
              athleteid
       ) minmax
       INNER JOIN training_route trmin ON trmin.athleteid = minmax.athleteid 
                                          AND trmin.minrowid = minmax.rowid
       INNER JOIN training_route trmax ON trmax.athleteid = minmax.athleteid 
                                          AND trmax.maxrowid = minmax.rowid

想法是

• 获取每个运动员的最小和最大 rowid
• 再次将之前的结果与表格结合以获得最小 rowid 的 gps 坐标
• 并再次加入原始表以获得最大 rowid 的 gps 坐标