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

[jeremyyang824]

大家好，我有一张实时监控设备停机状况的表，其中记录了：
设备编码、设备停机开始时间、设备停机结束时间（若该设备当前正停机，则结束时间为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 1760

NO.29 ECOFORCE 2HT3 2013-07-25 06:45:00.000 NULL NULL

[Shawn]

--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
*/

[jeremyyang824]

引用 1 楼 wwwwgou 的回复:

--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
*/

出差一个礼拜刚回来，谢谢了~