mysql:如何获取同一表中某一列的最后一个值和另一个列的第一个值？

Question

如何通过一次查询仅从下表中检索以下数据

1. MIN(采样开始)-
2. 第一个起始地址//[WHERE StartAddress = MIN(SamplingStart)] -
3. 最后一个起始地址//[WHERE StartAddress = MAX(SamplingStart)]

这是我到目前为止的代码。问题出在上面的第 3 点上。我怎样才能让 3 不工作而 1,2 仍然工作。

**The result must look like this.**

+-----------+--------------------+----------------+
| StartTime | StartAddress       | StopAddress    |
+-----------+--------------------+----------------+
| 08:00:00  | 26 Riverside Drive | Paterson       |
+-----------+--------------------+----------------

**Existing Code to modify.**

SELECT 

MIN(SamplingStart) AS StartTime,
StartAddress AS StartAddress, 
StopAddress AS StopAddress

FROM table1;


****table1****
+---------------+--------------------+--------------------+
| SamplingStart | StartAddress       | StopAddress        |
+---------------+--------------------+--------------------+
| 08:00:00      | 26 Riverside Drive | 70 Piet Retief     |
| 09:00:00      | 10 Glen Drive      | 16 Olga Street     |
| 10:00:00      | 66 Bremerhof       | 26 Bluewater Drive |
| 11:00:00      | 7 New Street       | Walker Drive       |
| 13:00:00      | Goodwood Street    | Blouberg           |
| 15:00:00      | Marine Drive       | William Moffet     |
| 18:00:00      | Jumanji            | Paterson           |
+---------------+--------------------+--------------------

Answer 1

您可以使用条件聚合:

SELECT MIN(SamplingStart) AS StartTime,
       MAX(CASE WHEN SamplingStart = StartTime THEN StartAddress END) AS StartAddress, 
       MAX(CASE WHEN SamplingStart = EndTime THEN StopAddress END) AS StopAddress
FROM table1 t1 CROSS JOIN
     (SELECT MIN(SamplingStart) AS StartTime, MAX(SamplingStart) AS ENDTime
      FROM table1
     ) tt