如果我正确理解发布的问题，那么您的CTE会有效地确定所有警报的时间段（或时间间隔）。您的最终select子句将实际的警报信息与您的警报间隔结合在一起。问题的一部分是，如果您的警报长时间处于活动状态（我假设比您的警报扫描周期长），那么警报系统将继续记录“扫描的警报”条目，这实际上会导致划分活动警报。如果您具有SQL
Server
2012或更高版本，则比较容易确定警报事件是否被拆分。您只需要检查警报的结束时间是否等于相同警报类型的下一个警报的开始时间。您可以在2012年使用LAG窗口功能来实现这一目标。
下一步是生成一个ID，您可以对警报进行分组，以便可以合并拆分事件。这是通过SUM OVER子句实现的。以下示例显示了如何实现此目的：
;WITH AlarmTimeBucketsAS ( SELECT EventStart.Ev_Comment AS StartDateTime ,MIN(COALESCE (EventEnd.Ev_Comment, EventStart.Ev_Comment)) AS EndDateTime ,EventStart.Ev_Message As Machine FROM A EventStart INNER JOIN A EventEnd ON EventStart.Ev_Comment < EventEnd.Ev_Comment AND EventStart.Ev_Custom1 = 'Alarms Scanned' AND EventEnd.Ev_Custom1 = 'Alarms Scanned' AND EventStart.Ev_Message = EventEnd.Ev_Message GROUP BY EventStart.Ev_Message, EventStart.Ev_Comment),AlarmsByTimeBucketAS( SELECt AlarmTimeBuckets.Machine ,AlarmTimeBuckets.StartDateTime ,AlarmTimeBuckets.EndDateTime ,Alarm.Ev_Custom1 AS Alarm ,( CASE WHEN LAG(AlarmTimeBuckets.EndDateTime, 1, NULL) OVER (PARTITION BY Alarm.Ev_Custom1,Alarm.Ev_Message ORDER BY AlarmTimeBuckets.StartDateTime) = AlarmTimeBuckets.StartDateTime THEN 0 ELSE 1 END ) AS IsNewEvent FROM A Alarm INNER JOIN AlarmTimeBuckets ON Alarm.Ev_Message = AlarmTimeBuckets.Machine AND Alarm.Ev_Comment = AlarmTimeBuckets.StartDateTime WHERe (Alarm.Ev_Custom1 <> 'Alarms Scanned')),AlarmsByGroupingIDAS( SELECt Machine ,StartDateTime ,EndDateTime ,Alarm ,SUM(IsNewEvent) OVER (ORDER BY Machine, Alarm, StartDateTime) AS GroupingID FROM AlarmsByTimeBucket)SELECt MAX(Machine) AS Machine ,MIN(StartDateTime) AS StartDateTime ,MAX(EndDateTime) AS EndDateTime ,MAX(Alarm) AS AlarmFROM AlarmsByGroupingIDGROUP BY GroupingIDORDER BY StartDateTime
