sql跨行计算间隔时长

      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