【高分求助】合并设备停机记录

Question

大家好，我有一张实时监控设备停机状况的表，其中记录了：
设备编码、设备停机开始时间、设备停机结束时间（若该设备当前正停机，则结束时间为NULL）、停机时长（若该设备当前正停机，则结束时间为NULL）。
由于某些原因，监控设备会把停机记录写成多条记录，即一次停机如果被写成了3条记录，则第1条记录的停机结束是第2条的停机开始；第2条记录的停机结束是第3条记录的停机开始...
现在我需要一个查询，能够查出一个视图，该视图将每个设备多条连续的停机记录合并成一条，请问这个查询怎么写？
正如上图的两种情况，应该分别是：
NO.29 ECOFORCE 2HT3     2013-07-22 16:38:01.000    2013-07-23 22:00:00.000  1760NO.29 ECOFORCE 2HT3     2013-07-25 06:45:00.000    NULL                     NULLSQL疑难高分

Answer 1

--DROP TABLE Monitor
CREATE TABLE Monitor
(
DownTime_id INT,
machine_name VARCHAR(100),
Start_Time DATETIME,
End_Time DATETIME,
Duration DECIMAL(10, 2)
)
INSERT Monitor
SELECT 1, 'a', '2013-01-01 1:00:00', '2013-01-01 2:00:00', 60 UNION ALL
SELECT 2, 'a', '2013-01-01 2:00:00', '2013-01-01 3:00:00', 60 UNION ALL
SELECT 3, 'a', '2013-01-01 3:00:00', NULL, NULL UNION ALL
SELECT 4, 'a', '2013-01-01 8:00:00', '2013-01-01 9:00:00', 60 UNION ALL
SELECT 5, 'a', '2013-01-01 9:00:00', '2013-01-01 10:00:00', 60 UNION ALL
SELECT 6, 'b', '2013-01-01 1:00:00', '2013-01-01 2:00:00', 60--功能是实现了，但效率可能不好。期待更好的算法
--索引1： machine_name, Start_Time
--索引1： machine_name, End_Time
--其它： 根据视图外部的条件，建立合适的索引;WITH cte AS --#1.先找出连续的最后那条记录
(
SELECT *, flag = 1
FROM Monitor A
WHERE NOT EXISTS
(
SELECT 1
FROM Monitor B
WHERE B.machine_name = A.machine_name
AND B.Start_Time = A.End_Time
)
),
cte1 AS --#2.根据最后一条记录递归出所有的连续记录,并将它们放到一个组中(以Downtime_id分组)
(
SELECT * FROM cte
UNION ALL
SELECT a.Downtime_id, b.machine_name, b.Start_Time, b.End_time, b.Duration, flag = 0
FROM cte1 a
INNER JOIN Monitor b
ON a.machine_name = b.machine_name
AND a.Start_Time = b.End_Time
)
SELECT
a.Downtime_id,
a.machine_name,
Start_Time = (SELECT MIN(Start_Time) FROM cte1 b WHERE b.DownTime_id = a.DownTime_id), --#3.把最后这条记录的Start_Time替换成最小的
a.End_time,
a.Duration
FROM cte a/*
Downtime_id machine_name Start_Time End_time Duration
3 a 2013-01-01 01:00:00.000 NULL NULL
5 a 2013-01-01 08:00:00.000 2013-01-01 10:00:00.000 60.00
6 b 2013-01-01 01:00:00.000 2013-01-01 02:00:00.000 60.00
*/