SELECT *,
DATEDIFF(时间单位(时/分/秒),LAG(下一条记录时间字段,1) OVER(PARTITION BY 分组字段 ORDER BY 排序字段),当前时间字段) AS 间隔时间
FROM 表名
例:当前这条数据时间CreateDateDown,下一条数据时间CreateDateUp之间的时间差
DECLARE @Temp TABLE
(
id INT IDENTITY(1,1),
TestName NVARCHAR(50),
TestSN NVARCHAR(50),
CreateDateUp DATETIME,
CreateDateDown DATETIME
)
INSERT @Temp
(
TestName,
TestSN,
CreateDateUp,
CreateDateDown
)
VALUES
(
N'ceshi1',
'TestSN001' ,
'2022-06-13 09:00:00.000' ,
'2022-06-13 09:10:00.000'
),
(
N'ceshi2',
'TestSN001' ,
'2022-06-13 09:39:00.000' ,
'2022-06-13 09:50:00.000'
),
(
N'ceshi3',
'TestSN001' ,
'2022-06-13 10:20:00.000' ,
'2022-06-13 10:40:00.000'
)
--时间差
SELECT *,
DATEDIFF(n,LAG(CreateDateDown,1) OVER(PARTITION BY TestSN ORDER BY CreateDateDown ASC,CreateDateUp ASC),CreateDateUp) AS DAY
FROM @Temp
输出结果:因为第一条记录没有对照时间,所以为空
id | TestName | TestSN | CreateDateUp | CreateDateDown | DAY(时间差) |
1 | ceshi1 | TestSN001 | 2022-06-13 09:00:00.000 | 2022-06-13 09:10:00.000 | NULL |
2 | ceshi2 | TestSN001 | 2022-06-13 09:39:00.000 | 2022-06-13 09:50:00.000 | 29 |
3 | ceshi3 | TestSN001 | 2022-06-13 10:20:00.000 | 2022-06-13 10:40:00.000 | 30 |