一旦确定了行的顺序，就将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 data
WITH 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 end
SELECT * 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
) z
ORDER BY COALESCE(StartTime,EndTime), AlarmNumber
602392714
清零编程群