SQL区间查询：如何提取工序起止时间与关联加工名称

我目前在SQL中有一份极长的记录列表，数据以开始和结束状态作为分界，中间存在重复的加工名称取值。我需要查询获取所有工序对应的开始日期、结束日期以及关联的加工名称，请问该如何实现？

当前使用的查询语句 #

SELECT TOP 1000 id_prod,Descrizione,Parm0,LEFT(parm1, LEN(parm1)-20) as Azione ,convert(DateTime,right(parm1,19)) as Orario FROM INPUT
where
    Parm0 = 'programstate' and (charindex('DNC_PRG_STS_FINISHED', Parm1) > 0)
         or
     Parm0 = 'programevent' and (charindex('dnc_prg_evt_started', Parm1) > 0 )
      or
      (
          parm0='ProgramName' and (charindex('USB0', Parm1) > 0)
              )
order by Orario Desc

当前查询结果示例 #

补充说明 #

感谢@LukStorms的回复，但该方案不符合我的需求。目前符合「开始-加工代码-结束」分组的记录有数千条，我最终需要输出如下格式的列表：

希望我的需求表述清晰：这类工序共有数千条，我需要提取每个工序的开始时间、结束时间和中间的加工名称，最终输出为便于阅读的表格形式。

实现逻辑 #

你遇到的是典型的时间序列事件分组问题，每个工序由开始事件触发、结束事件终止，中间的ProgramName记录对应当前工序的加工名称。我们通过窗口函数对事件打分组标记，再聚合得到每个工序的完整信息，方案完全兼容你当前使用的SQL Server语法。

完整查询代码 #

WITH event_data AS (
    -- 预处理数据，标记三类事件：开始/结束/加工名称
    SELECT 
        id_prod,
        Parm0,
        LEFT(parm1, LEN(parm1)-20) as Azione,
        CONVERT(DATETIME, RIGHT(parm1,19)) as Orario,
        CASE 
            WHEN Parm0 = 'programevent' AND CHARINDEX('dnc_prg_evt_started', Parm1) > 0 THEN 1
            WHEN Parm0 = 'programstate' AND CHARINDEX('DNC_PRG_STS_FINISHED', Parm1) > 0 THEN 2
            WHEN Parm0 = 'ProgramName' AND CHARINDEX('USB0', Parm1) > 0 THEN 0
        END AS event_type
    FROM INPUT
    WHERE 
        (Parm0 = 'programstate' AND CHARINDEX('DNC_PRG_STS_FINISHED', Parm1) > 0)
        OR (Parm0 = 'programevent' AND CHARINDEX('dnc_prg_evt_started', Parm1) > 0 )
        OR (Parm0='ProgramName' AND CHARINDEX('USB0', Parm1) > 0)
),
grouped_events AS (
    -- 按时间排序，每遇到一个开始事件就生成新的工序分组ID
    SELECT 
        *,
        SUM(CASE WHEN event_type = 1 THEN 1 ELSE 0 END) OVER (ORDER BY Orario ASC) AS process_group
    FROM event_data
)
-- 聚合每个分组得到目标格式结果
SELECT 
    ROW_NUMBER() OVER (ORDER BY MIN(Orario) ASC) AS id_prod,
    MIN(CASE WHEN event_type = 1 THEN Orario END) AS Start,
    MAX(CASE WHEN event_type = 2 THEN Orario END) AS Finish,
    MAX(CASE WHEN event_type = 0 THEN Azione END) AS Lavorazione
FROM grouped_events
GROUP BY process_group
-- 过滤未匹配到开始/结束的无效工序分组，不需要可删除
HAVING MIN(CASE WHEN event_type = 1 THEN Orario END) IS NOT NULL
AND MAX(CASE WHEN event_type = 2 THEN Orario END) IS NOT NULL
ORDER BY Start ASC

注意事项 #

1. 同工序内如果存在多个不同的加工名称，当前取的是最后出现的名称，你可以根据业务需要把MAX改成MIN取第一个出现的名称
2. 如果你需要保留只有开始没有结束、或者只有结束没有开始的异常工序数据，直接删除HAVING段的过滤条件即可

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