mysql - mysql:如何获取一个特定列的最后一个值作为子查询的一部分

我有下表。

+--------------------+--------+------+------------+----------+---------------------+
| StopAddress        | UserID | Name | StartHours | EndHours | SamplingEnd         |
+--------------------+--------+------+------------+----------+---------------------+
| Legends            |      1 | Lisa | 09:00:00   | 08:00:59 | 2016-03-22 09:00:00 |
| 26 Bluewater Drive |      1 | Lisa | 10:00:00   | 08:00:59 | 2016-03-22 10:00:00 |
| Walker Drive       |      1 | Lisa | 11:00:00   | 08:00:59 | 2016-03-22 11:00:00 |
| Blouberg           |      1 | Lisa | 12:00:00   | 08:00:59 | 2016-03-22 12:00:00 |
| William Moffet     |      1 | Lisa | 13:00:00   | 08:00:59 | 2016-03-22 13:00:00 |
| Paterson           |      1 | Lisa | 14:00:00   | 08:00:59 | 2016-03-22 14:00:00 |
| 70 Kings Street    |      1 | Lisa | 15:00:00   | 08:00:59 | 2016-03-22 15:00:00 |
| Pick n Pay         |      1 | Lisa | 16:00:00   | 08:00:59 | 2016-03-22 16:00:00 |
| Mars               |      1 | Lisa | 17:00:00   | 08:00:59 | 2016-03-22 17:00:00 |
| Club Shakes        |      1 | Lisa | 18:00:00   | 08:00:59 | 2016-03-22 18:00:00 |
| Stones             |      1 | Lisa | 19:00:00   | 08:00:59 | 2016-03-22 19:00:00 |
| Ermelo             |      1 | Lisa | 20:00:00   | 08:00:59 | 2016-03-22 20:00:00 |
| Angelos            |      1 | Lisa | 05:00:00   | 00:20:16 | 2016-03-23 05:00:00 |
| Game               |      1 | Lisa | 05:00:00   | 00:20:16 | 2016-03-23 04:00:00 |
+--------------------+--------+------+------------+----------+---------------------+

当我运行下面的查询时，得到下面的结果。（见“当前结果”）
查询：

SELECT Min(samplingend) AS StartTime,
       Min(starthours)  AS StartHours,
       Max(endhours)    AS EndHours,
       Max(samplingend) AS LastStart,
       (SELECT stopaddress
        FROM   table1
        ORDER  BY samplingend DESC
        LIMIT  1)       AS StopAddress
FROM   table1
GROUP  BY userid,
          name,
          Date(samplingend)
ORDER  BY Date(samplingend),
          userid;

当前结果：

 +---------------------+------------+----------+---------------------+-------------+
 | StartTime           | StartHours | EndHours | LastStart           | StopAddress |
 +---------------------+------------+----------+---------------------+-------------+
 | 2016-03-22 09:00:00 | 09:00:00   | 08:00:59 | 2016-03-22 20:00:00 | Angelos     |
 | 2016-03-23 04:00:00 | 05:00:00   | 00:20:16 | 2016-03-23 05:00:00 | Angelos     |
 +---------------------+------------+----------+---------------------+-------------+

预期结果：
当您运行查询时，它必须给出StopAddress，其中StopAddress=Max（SamplingEnd）是每天分组的。例如，在下面的例子中，在第22个StopAddress上应该是“Ermelo”，在第23个StopAddress上应该是“Angelos”。是否可以通过仅在第6行中更改代码而不使用表别名来实现此结果。例如不使用表1 T1或T2？第6行：（从表1按采样顺序选择StopAddress DESC limit 1）作为StopAddress）
结果示例：（查看StopAddress列）

 +---------------------+------------+----------+---------------------+-------------+
 | StartTime           | StartHours | EndHours | LastStart           | StopAddress |
 +---------------------+------------+----------+---------------------+-------------+
 | 2016-03-22 09:00:00 | 09:00:00   | 08:00:59 | 2016-03-22 20:00:00 | Ermelo     |
 | 2016-03-23 04:00:00 | 05:00:00   | 00:20:16 | 2016-03-23 05:00:00 | Angelos     |
 +---------------------+------------+----------+---------------------+-------------+

谢谢你的时间！!

最佳答案

需要包含表别名才能使用外部值

SELECT Min(samplingend) AS StartTime,
       Min(starthours)  AS StartHours,
       Max(endhours)    AS EndHours,
       Max(samplingend) AS LastStart,
       (SELECT T2.stopaddress
        FROM   table1  T2
        WHERE  Date(T2.samplingend) = Date(T1.samplingend) -- ADD THIS WHERE
        ORDER  BY T2.samplingend DESC
        LIMIT  1)       AS StopAddress
FROM   table1  T1
GROUP  BY userid,
          name,
          Date(samplingend)
ORDER  BY Date(samplingend),
          userid;

关于mysql - mysql:如何获取一个特定列的最后一个值作为子查询的一部分，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36338005/