如果这真的是您的数据，您可以简单地使用INNER JOIN和DATEDIFF：

SELECT MAX(DATEDIFF(MINUTE, T1.[DateTime], T2.[DateTime]))
FROM [my_table] T1
INNER JOIN [my_table] T2
    ON T1.[Row] + 1 = T2.[Row];

这是间隙和岛问题，解决它的一个选项是使用每当machine_on = 0时增加1的运行和，这将为后跟0的连续1定义唯一组。

select top 1 datediff(minute, min([datetime]), max([datetime])) duration
from
(
  select *, 
   sum(case when machine_on = 0 then 1 else 0 end) over (order by datetime desc) grp
  from table_name
) T
group by grp
order by datediff(minute, min([datetime]), max([datetime])) desc

See demo

下面是另一种使用传统差距和孤岛方法的方法：

WITH src AS
(
  SELECT Island, mint = MIN([Timestamp]), maxt = MAX([Timestamp])
  FROM
  (
    SELECT [Timestamp], Island = 
      ROW_NUMBER() OVER (ORDER BY [Timestamp]) -
      ROW_NUMBER() OVER (PARTITION BY Running ORDER BY [Timestamp])
    FROM dbo.Machine_Status
  ) AS x GROUP BY Island
)
SELECT TOP (1) delta = 
   (DATEDIFF(second, mint, LEAD(mint,1) OVER (ORDER BY island)))
 FROM src ORDER BY delta DESC;

• 示例db<>fiddle基于your new duplicate中的示例数据。

这是一个经典的差距和岛屿有点扭曲Adj

• • 示例**

Select Top 1
       Row1 = min(row)
      ,Row2 = max(row)+1
      ,TS1  = min(TimeStamp)
      ,TS2  = dateadd(SECOND,max(Adj),max(TimeStamp))
      ,Dur = datediff(Second,min(TimeStamp),max(TimeStamp)) + max(Adj)
 From (
        Select * 
              ,Grp = row_number() over( partition by Running order by TimeStamp) - row_number() over (order by timeStamp)
              ,Adj  = case when Running=1 and lead(Running,1) over (order by timestamp) = 0  then datediff(second,TimeStamp,lead(TimeStamp,1) over (order by TimeStamp) ) else 0 end
         From Machine_Status
      ) A
 Where Running=1
 Group By Grp
 Order By Dur Desc

• • 结果**

Row1    Row2    TS1                     TS2                       Dur
8       12      2023-01-10 08:25:30.000 2023-01-10 08:28:55.000   205