34,590
社区成员
发帖
与我相关
我的任务
分享
DECLARE @a table(id int, f1 varchar(20),f2 varchar(20),f3 varchar(20),f4 varchar(20))
INSERT @a SELECT 1 ,'1:10','1:20','1:30','2008-1-3'
UNION ALL SELECT 2 ,'4:20', '6:18', '9:25', '2008-1-5'
UNION ALL SELECT 3 ,'2:05', '2:23', '2:40', '2008-1-4'
SELECT id,
f4,
[f2-f1]=datediff(minute,f1,f2),
[f3-f2]=datediff(minute,f2,f3),
[f1-f3]=datediff(minute,f3,(SELECT TOP 1 f1 FROM @a WHERE convert(datetime,f4)>CONVERT(datetime,a.f4) ORDER BY convert(datetime,f4)))
FROM @a a
ORDER BY convert(datetime,f4)
--result
/*
id f4 f2-f1 f3-f2 f1-f3
----------- -------------------- ----------- ----------- -----------
1 2008-1-3 10 10 35
3 2008-1-4 18 17 100
2 2008-1-5 118 187 NULL
(所影响的行数为 3 行)*/