如何选择最小值和下一个分钟，并在同一行上显示与相关查询的同一行

Question

我使用了此SQL：

/****** Select Min and Next Min with Bike ID  ******/
SELECT
t.[bike_id] 
,min(t.[start_date]) as Min_Date
,(SELECT min([start_date]) From trip Where [start_date] > (SELECT min([start_date]) From trip)) as Next_Min_Date
FROM [dbo].[trip] t
Group By t.[bike_id] 
Having t.[bike_id] < '15'

并得到了这个结果：

+---------+-------------------------+-------------------------+
| bike_id | Min_Date                | Next_Min_Date           |
+---------+-------------------------+-------------------------+
| 9       | 2013-08-29 12:02:00.000 | 2013-08-29 09:24:00.000 |
| 10      | 2013-09-01 13:47:00.000 | 2013-08-29 09:24:00.000 |
| 11      | 2013-08-29 20:11:00.000 | 2013-08-29 09:24:00.000 |
| 12      | 2013-08-30 17:15:00.000 | 2013-08-29 09:24:00.000 |
| 13      | 2013-08-29 10:13:00.000 | 2013-08-29 09:24:00.000 |
| 14      | 2013-08-31 11:55:00.000 | 2013-08-29 09:24:00.000 |
+---------+-------------------------+-------------------------+

显然，子查询没有相关 - 但我不确定如何使其成为相关的子查询

我想要的是每个自行车ID 的下一个分钟值不仅是表中的第二分钟值

Answer 1

您可以使用row_number来执行此操作。

select [bike_id],
max(case when rnum=1 then t.[start_date] end) as min_date,
max(case when rnum=2 then t.[start_date] end) as next_min_date
from (
SELECT
t.*,row_number() over(partition by t.[bike_id] order by t.[start_date]) as rnum
FROM [dbo].[trip] t
) x
group by [bike_id]