如何通过SQL根据表中数据状态计算状态切换的时间间隔？

现有一张持续接收数据的表dbo.CL，包含LINE、TIMESTAMP、CL_Name、Status字段。需求为：将Status=0的最早TIMESTAMP作为StartTime，Status>0的最晚TIMESTAMP作为EndTime，计算二者时间差并生成指定结构的结果表，但当前SQL查询未得到预期结果。

原表数据 #

目标结果表 #

当前错误SQL语句 #

SELECT [LINE], [CL_Name], StartTime, EndTime, 
      DATEDIFF(second, startTime, endTime) as TotalTime
 FROM ( select [LINE], [CL_Name], [TIMESTAMP], [Status],
    Min(case when [Status] > 0 then [TIMESTAMP] end) as EndTime,
    Min(case when [Status] = 0 then [TIMESTAMP] end) as StartTime
  from [dbo].[CL]
  group by [LINE], [CL_Name], [_VALUE], [TIMESTAMP], [Status]) as tt
  where StartTime IS NOT NULL and EndTime IS NOT NULL
  Order by [TIMESTAMP] desc

当前错误输出 #

问题分析与修正方案 #

错误原因 #

1. 分组字段冗余错误：原SQL将TIMESTAMP、Status及不存在的[_VALUE]加入分组条件，导致每条原始记录单独成组，无法聚合出全局的StartTime和EndTime。正确分组应仅保留LINE和CL_Name，确保同组数据为同一生产线同一设备的记录。
2. 聚合函数误用：求EndTime时使用MIN()，但需求是取Status>0的最晚时间，应改用MAX()。

修正后的SQL #

SELECT 
    [LINE],
    [CL_Name],
    StartTime,
    EndTime,
    CONCAT(DATEDIFF(minute, StartTime, EndTime), 'Min') AS TotalTime
FROM (
    SELECT 
        [LINE],
        [CL_Name],
        MIN(CASE WHEN [Status] = 0 THEN [TIMESTAMP] END) AS StartTime,
        MAX(CASE WHEN [Status] > 0 THEN [TIMESTAMP] END) AS EndTime
    FROM [dbo].[CL]
    GROUP BY [LINE], [CL_Name]
) AS tt
WHERE StartTime IS NOT NULL AND EndTime IS NOT NULL;

说明 #

• 内层查询仅按LINE和CL_Name分组，通过CASE配合聚合函数分别提取Status=0的最早时间和Status>0的最晚时间。
• 外层查询计算时间差并格式化为"XXMin"的形式，同时过滤掉StartTime或EndTime为空的组（避免无有效状态记录的情况）。

内容的提问来源于stack exchange，提问作者burak