一旦确定了行的顺序，就将
SELECt它们分成几部分，并使用合并结果
UNIOn ALL：
DECLARE @DataSource TABLE( [AlarmNumber] INT ,[Time] DATETIME2(0) ,[AlarmState] INT);INSERT INTO @DataSource ([AlarmNumber], [Time], [AlarmState])VALUES (1592, '2020-01-02 01:52:02', 1) ,(1592, '2020-01-02 01:58:07', 0) ,(1592, '2020-04-28 03:46:49', 1) ,(1592, '2020-04-28 06:19:10', 0) ,(1592, '2020-06-04 00:25:22', 1) ,(1592, '2020-08-27 01:57:03', 1) ,(1592, '2020-08-27 05:16:32', 0) ,(1592, '2020-09-17 02:51:57', 0);-- Add a rowID column to the dataWITH DataSource AS( SELECT * ,ROW_NUMBER() Over(Partition by AlarmNumber order by [Time]) rowID FROM @DataSource)-- This is just here so we can sort the result at the endSELECt * FROM (-- Select rows of DataSource where there is an ON and subsequent OFF event (DS1 Alarm is ON and DS2 Alarm is OFF)-- This also catches where there is an ON, but no subsequent OFF (DS2.Time will be NULL) SELECT DS1.AlarmNumber ,DS1.Time As StartTime ,DS2.Time As EndTime FROM DataSource DS1 LEFT JOIN DataSource DS2 ON DS1.[rowID] = DS2.[rowID] - 1 AND DS1.AlarmNumber = DS2.AlarmNumber AND DS2.[AlarmState] = 0 WHERe DS1.[AlarmState] = 1 UNIOn ALL -- Select rows of DataSource where there is an OFF and there is no matching ON (aka it turned OFF without ever turning ON) SELECT DS2.AlarmNumber ,NULL As StartTime ,DS2.Time As EndTime FROM DataSource DS2 INNER JOIN DataSource DS1 ON DS2.[rowID] -1 = DS1.[rowID] AND DS1.[AlarmState] = 0 AND DS2.AlarmNumber = DS1.AlarmNumber WHERe DS2.[AlarmState] = 0 UNIOn ALL -- Select rows of DataSource where the first event for this alarm number is an OFF (it would otherwise be missed by the above) SELECT DS1.AlarmNumber ,NULL As StartTime ,DS1.Time As EndTime FROM DataSource DS1 WHERe DS1.[AlarmState] = 0 AND DS1.rowID = 1) zORDER BY COALESCE(StartTime,EndTime), AlarmNumber
