SQL Select语句，返回来自同一个表的下一个日期时间的分钟差异(SQL Select statements that returns differences in minutes from ne

SQL Select语句，返回来自同一个表的下一个日期时间的分钟差异(SQL Select statements that returns differences in minutes from next date-time from the same table)

我有一个用户活动表。 我想在几分钟内看到每个过程之间的差异。 这里更具体的是表中的部分数据。

Date |TranType| Prod | Loc ----------------------------------------------------------- 02/27/12 3:17:21 PM | PICK | LIrishGreenXL | E01C015 02/27/12 3:18:18 PM | PICK | LAntHeliconiaS | E01A126 02/27/12 3:19:00 PM | PICK | LAntHeliconiaL | E01A128 02/27/12 3:19:07 PM | PICK | LAntHeliconiaXL | E01A129

我想在几分钟内检索时差，在第一个和第二个过程之间，而不是第二个和第三个过程....谢谢

I have a user activity table. I want to see a difference between each process in minutes. To be more specific here is a partial data from table.

Date |TranType| Prod | Loc ----------------------------------------------------------- 02/27/12 3:17:21 PM | PICK | LIrishGreenXL | E01C015 02/27/12 3:18:18 PM | PICK | LAntHeliconiaS | E01A126 02/27/12 3:19:00 PM | PICK | LAntHeliconiaL | E01A128 02/27/12 3:19:07 PM | PICK | LAntHeliconiaXL | E01A129

I want to retrieve time difference in minutes, between first and second process, than second and third and .... Thank you

最满意答案

这样的东西将在MS SQL中工作，只需更改字段名称以匹配您的：

select a.ActionDate, datediff(minute,a.ActionDate,b.ActionDate) as DiffMin

(select ROW_NUMBER() OVER(ORDER BY ActionDate) AS Row, ActionDate
from [dbo].[Attendance]) a

inner join 

(select ROW_NUMBER() OVER(ORDER BY ActionDate) AS Row, ActionDate
from [dbo].[Attendance]) b

on a.Row +1  = b.Row  
  
 
Something like this will work in MS SQL, just change the field names to match yours:
 
 
 
  
  
select a.ActionDate, datediff(minute,a.ActionDate,b.ActionDate) as DiffMin

(select ROW_NUMBER() OVER(ORDER BY ActionDate) AS Row, ActionDate
from [dbo].[Attendance]) a

inner join 

(select ROW_NUMBER() OVER(ORDER BY ActionDate) AS Row, ActionDate
from [dbo].[Attendance]) b

on a.Row +1  = b.Row