如何通过单条SQL查询获取以下一条start_time为end_time的结果？

现有SQL表数据如下：

| id | price | start_time |
---------------------------
|  1 |  0.1  | 2023-01-01 |
|  2 |  0.3  | 2023-03-01 |
|  3 |  0.2  | 2023-02-01 |

需求是查询该表时为每条记录添加end_time字段，取值为按start_time排序后的下一条记录的start_time，具体分两种场景：

• 查询全表时，结果需如下：

| id | price | start_time | end_time   |
----------------------------------------
|  1 |  0.1  | 2023-01-01 | 2023-02-01 | // end_time = 下一条记录的start_time
|  3 |  0.2  | 2023-02-01 | 2023-03-01 |
|  2 |  0.3  | 2023-03-01 |            |

• 添加过滤条件（比如price < 0.25）时，即使被过滤的记录（如id=2），其start_time仍需作为上一条符合条件记录的end_time，预期结果如下：

| id | price | start_time | end_time   |
----------------------------------------
|  1 |  0.1  | 2023-01-01 | 2023-02-01 |
|  3 |  0.2  | 2023-02-01 | 2023-03-01 | // end_time = id=2记录的start_time

请问能否通过单条SQL查询实现该需求？

可以通过单条SQL实现该需求，核心思路是先基于全表按时间排序计算出每条记录的下一个时间节点，再对结果应用过滤条件，这样被过滤的记录的时间依然能作为前置符合条件记录的end_time。

方案一：使用窗口函数（适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

WITH ordered_times AS (
    SELECT 
        id, 
        price, 
        start_time,
        LEAD(start_time) OVER (ORDER BY start_time) AS end_time
    FROM your_table_name -- 替换为你的实际表名
)
SELECT id, price, start_time, end_time
FROM ordered_times
WHERE price < 0.25; -- 全表查询时直接去掉WHERE子句即可

逻辑说明：

1. 用WITH子句生成临时数据集ordered_times：通过LEAD()窗口函数，按start_time升序排列，为每条记录获取排序后的下一条记录的start_time作为end_time，最后一条记录的end_time会是NULL。
2. 对临时数据集应用过滤条件，此时过滤操作不会改变已经计算好的end_time，因为end_time是基于全表排序得到的，哪怕后续记录被过滤，前置记录的end_time依然保留了原本的下一个时间节点。

方案二：自关联查询（适用于不支持窗口函数的旧版本数据库） #

SELECT 
    t1.id, 
    t1.price, 
    t1.start_time,
    MIN(t2.start_time) AS end_time
FROM your_table_name t1
LEFT JOIN your_table_name t2 ON t2.start_time > t1.start_time
GROUP BY t1.id, t1.price, t1.start_time
HAVING t1.price < 0.25; -- 全表查询时去掉HAVING子句即可

逻辑说明：

1. 将表自关联，关联条件设为t2.start_time > t1.start_time，找到所有比当前记录时间晚的记录。
2. 用MIN(t2.start_time)获取这些记录中最早的时间，也就是排序后的下一个时间节点，作为当前记录的end_time。
3. 最后通过HAVING（或WHERE）过滤符合条件的记录，计算end_time时是基于全表数据，所以被过滤的记录的时间依然能被纳入计算。

内容的提问来源于stack exchange，提问作者Manuelarte