从表中的一列sql server生成日期差异

Question

Shift    Time                    Action
TA      2021-03-17 10:39:11.613    OF
TA      2021-03-17 11:09:11.353    ON
TA      2021-03-17 13:49:20.760    OF
TA      2021-03-17 14:10:20.760    ON

嗨，我想知道你能不能帮我。我有一张输入和输出表。我记录它发生的时间和日期。但出于报告目的，我需要对该列进行日期差异处理。任何人都知道我如何生成查询以获得以下结果。

Shift    Time                    Action       Shift    Time                    Action
TA      2021-03-17 10:39:11.613    OF          TA       2021-03-17 11:09:11.353    ON
TA      2021-03-17 13:49:20.760    OF          TA       2021-03-17 14:10:20.760    ON

Answer 1

假设值正好交错，则可以使用lead()，然后过滤：

select *
from (select shift, time, action,
lead(time) over (partition by shift order by time) as next_time,
lead(action) over (partition by shift order by time) as next_action
from t
) t
where action = 'OF';