sql - 从表中的单个列生成日期差异，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

标签： sqlsql-server

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';