sql server 计算同一列两行的时间差

Question

sql server 计算同一列两行的时间差：

数据库信息如下：
ID name DateTime
1 14774 2022-02-08 19:35:26.137
2 16284 2022-02-09 08:14:35.220
3 16284 2022-02-09 08:14:33.883
4 14774 2022-02-09 08:14:33.547
5 16284 2022-02-09 08:14:33.543
6 14774 2022-02-09 08:14:32.080
7 16284 2022-02-09 08:14:32.077
8 16284 2022-02-09 08:14:32.073
9 16284 2022-02-09 08:14:32.067

需求如下：

1. 按照“name”列进行分组
2. 计算上一行和下一行的时间差

Answer 1

是这个意思吗？

declare @a table(ID int, name varchar(20), DateTime datetime)
insert @a 
select 1, '14774', '2022-02-08 19:35:26.137'
union all select 2 ,'16284', '2022-02-09 08:14:35.220'
union all select 3 ,'16284', '2022-02-09 08:14:33.883'
union all select 4 ,'14774', '2022-02-09 08:14:33.547'
union all select 5 ,'16284', '2022-02-09 08:14:33.543'
union all select 6 ,'14774', '2022-02-09 08:14:32.080'
union all select 7 ,'16284', '2022-02-09 08:14:32.077'
union all select 8 ,'16284', '2022-02-09 08:14:32.073'
union all select 9 ,'16284', '2022-02-09 08:14:32.067'

select id,
    name,
    datetime,d=DATEDIFF(ms,datetime,(select top 1 datetime from @a where id>a.id and name=a.name order by id))
from @a a 
order by name,id

--result
1    14774    2022-02-08 19:35:26.137    45547410
4    14774    2022-02-09 08:14:33.547    -1466
6    14774    2022-02-09 08:14:32.080    NULL
2    16284    2022-02-09 08:14:35.220    -1336
3    16284    2022-02-09 08:14:33.883    -340
5    16284    2022-02-09 08:14:33.543    -1466
7    16284    2022-02-09 08:14:32.077    -3
8    16284    2022-02-09 08:14:32.073    -6
9    16284    2022-02-09 08:14:32.067    NULL