在sql中查找同一个表中连续两行的时间差 [英] Find the time difference between two consecutive rows in the same table in sql

问题描述

我被卡住了.我一直在寻找答案，但似乎无法从同一张表的两行不同的行中找到在同一张表中相减的时间.我在执行以下查询时遇到了困难.在下表中，我想将一行的 TimeOut 与下一行的 TimeIn 区分开来.考虑在下表中找出第 1 行中的 TimeOut(上午 10:35)和第 2 行中的 TimeIn(上午 10:38)之间的分钟差异.

I'm stuck. I've looked for an answer, but can't seem to find subtracting time in the same table from two different rows of the same table that fits. I'm having a difficult time with the following query. In the table below, I want to differentiate the TimeOut from one row to the TimeIn of the next row. Consider in the following table of finding the difference in minutes between the TimeOut in Row 1 (10:35am) and the TimeIn in Row 2 (10:38am).

表 1:TIMESHEET

ROW    EmpID       TimeIn                   TimeOut
----------------------------------------------------------------
1       138         2014-01-05 10:04:00      2014-01-05 10:35:00   
2       138         2014-01-05 10:38:00      2014-01-05 10:59:00 
3       138         2014-01-05 11:05:00      2014-01-05 11:30:00  

预期结果

ROW    EmpID       TimeIn                   TimeOut                  Minutes
----------------------------------------------------------------------------
1       138         2014-01-05 10:04:00      2014-01-05 10:35:00       
2       138         2014-01-05 10:38:00      2014-01-05 10:59:00       3
3       138         2014-01-05 11:05:00      2014-01-05 11:30:00       6
etc
etc
etc

基本上，我需要区分查询中的时间以显示员工休息的时间.

Basically, I need to differentiate the times in the query to show how long employees were on break.

我尝试过加入，但这似乎不起作用，我不知道 OVER 和 PARTITION 是否可行，因为我似乎无法遵循逻辑(是的，我仍在学习).我还考虑了两个临时表并比较它们，但是当我开始更改日期或员工 ID 时，这不起作用.最后，我想也许 LEAD 在 OVER 语句中?或者用 CAST 做一个 DATEDIFF 是否很简单?

I've tried doing a join, but that doesn't seem to work and I don't know if OVER with PARTITION is the way to go, because I cannot seem to follow the logic (Yeah, I'm still learning). I also considering two temp tables and comparing them, but that doesn't work when I start changing days or employee ID's. Finally, I am thinking maybe LEAD in an OVER statement? Or is it just simple to do a DATEDIFF with a CAST?

推荐答案

我已经解决了类似的问题，甚至不需要对行进行排序:

I have solved this for similar problems and it need not be that the rows even be sorted:

select t1.EmpID, t1.TimeIn, t1.TimeOut, 
       datediff(minute, max(t2.TimeOut), t1.TimeIn) as minutes
from timesheet t1 left join timesheet t2 on t1.EmpID = t2.EmpID 
       and t2.TimeOut < t1.TimeIn
group by t1.EmpID, t1.TimeIn, t1.TimeOut

让我知道这是否有效.

这是一个 sql 小提琴:http://sqlfiddle.com/#!3/89a43/1

Here is a sql fiddle: http://sqlfiddle.com/#!3/89a43/1

这篇关于在sql中查找同一个表中连续两行的时间差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！