T-sql求一个解法

[nettt]

这是一个的算法问题
要处理的是计算设备停机的一笔数据。
我学sql没多长时间，搞了好久没做出来
我先说明一下数据：
主要数据列 是 ‘设备开始运行时间’，‘设备号’，‘停机秒数’
算法：
1、每一个设备号的第一条记录的‘开始时间’做为这个设备的‘开机时间’，单独写入一个临时表的字段里。
2、开始逐条数据做对比，及row_,1，2条做对比，2，3做对比,3,4条做对比。。。。当遇到两条的时间差>720秒，例如，8，9条，那么，第8条的‘开始时间’+'停机秒数'就算一次停机。
整理出来的第一条数据如下
开机时间 停机时间 设备号
2012-09-16 00:55:53.350| 2012-09-16 01:00:21.733| 467
3、第9条数据会做为下一次的开机时间，然后继续逐条两两对比，如果该设备到了最后一条记录，也没有发现时间差>720的情况，那么最后一条记录 的‘开始时间’+‘停机秒数’就做为‘停机时间’

整理出来的第二条数据如下
开机时间 停机时间 设备号
2012-09-16 04:35:40.443| 2012-09-16 04:40:37.297| 467

4、继续对比，当第一个设备的最后一条记录和第二个设备的第一条记录相遇时，（例如13，14条）第二个设备的第一条记录的‘开始时间’就做为第二个设备的’开机时间‘
然后循环执行

下面是数据：


原始数据
row_ prday 开始时间 设备名 设备号 停机秒数
1 2012-09-15 2012-09-16 00:55:53.350 1-61-1 Hopper term 467 35
2 2012-09-15 2012-09-16 00:56:29.307 1-61-1 Hopper term 467 36
3 2012-09-15 2012-09-16 00:57:14.187 1-61-1 Hopper term 467 45
4 2012-09-15 2012-09-16 00:57:49.507 1-61-1 Hopper term 467 35
5 2012-09-15 2012-09-16 00:58:24.810 1-61-1 Hopper term 467 35
6 2012-09-15 2012-09-16 00:58:59.537 1-61-1 Hopper term 467 35
7 2012-09-15 2012-09-16 00:59:34.993 1-61-1 Hopper term 467 35
8 2012-09-15 2012-09-16 01:00:11.733 1-61-1 Hopper term 467 10
9 2012-09-15 2012-09-16 04:35:40.443 1-61-1 Hopper term 467 37
10 2012-09-15 2012-09-16 04:36:29.643 1-61-1 Hopper term 467 49
11 2012-09-15 2012-09-16 04:37:05.557 1-61-1 Hopper term 467 36
12 2012-09-15 2012-09-16 04:37:47.503 1-61-1 Hopper term 467 42
13 2012-09-15 2012-09-16 04:40:17.297 1-61-1 Hopper term 467 20

14 2012-09-15 2012-09-15 16:38:17.063 1-61-2 Hopper term 468 34
15 2012-09-15 2012-09-15 16:38:51.277 1-61-2 Hopper term 468 35
16 2012-09-15 2012-09-15 16:39:27.327 1-61-2 Hopper term 468 36
17 2012-09-15 2012-09-15 16:40:00.133 1-61-2 Hopper term 468 33
18 2012-09-15 2012-09-15 16:40:34.440 1-61-2 Hopper term 468 34
19 2012-09-15 2012-09-15 16:41:08.727 1-61-2 Hopper term 468 30
20 2012-09-15 2012-09-16 02:31:18.497 1-61-2 Hopper term 468 35
21 2012-09-15 2012-09-16 02:32:22.893 1-61-2 Hopper term 468 64
22 2012-09-15 2012-09-16 02:33:59.333 1-61-2 Hopper term 468 96
23 2012-09-15 2012-09-16 02:34:33.497 1-61-2 Hopper term 468 20
24 2012-09-15 2012-09-16 03:35:37.847 1-61-2 Hopper term 468 64
25 2012-09-15 2012-09-16 03:44:45.237 1-61-2 Hopper term 468 0
26 2012-09-15 2012-09-16 03:45:19.400 1-61-2 Hopper term 468 34
27 2012-09-15 2012-09-16 03:47:44.777 1-61-2 Hopper term 468 10

整理后的数据如下
原始id ID 开机时间 停机时间 设备号
1 1 2012-09-16 00:55:53.350 2012-09-16 01:00:21.733 467
9 2 2012-09-16 04:35:40.443 2012-09-16 04:40:37.297 467
14 3 2012-09-15 16:38:17.063 2012-09-15 16:41:48.727 468
20 4 2012-09-16 02:31:18.497 2012-09-16 02:34:53.497 468
24 5 2012-09-16 03:35:37.847 2012-09-16 03:47:54.777 468

我把数据库放在朋友的网站上了，可以下载 208KB。
http://honey58.com/p503.rar

[快溜]

嗯，问题写的很详细。row_ prday是连续的吗

[nettt]

row_,是连续的，是自增的，prday是日期，第天都不一样，

[nettt]

求解。。。。。。。。。。。。。

[playwarcraft]

Create table T(row  int, prday datetime, btime datetime , name varchar(100), number int, seconds int)
insert into T

          select 1 ,'2012-09-15', '2012-09-16 00:55:53.350', '1-61-1 Hopper term' ,467, 35
union all select 2, '2012-09-15', '2012-09-16 00:56:29.307', '1-61-1 Hopper term', 467, 36
union all select 3, '2012-09-15', '2012-09-16 00:57:14.187', '1-61-1 Hopper term', 467, 45
union all select 4, '2012-09-15', '2012-09-16 00:57:49.507', '1-61-1 Hopper term', 467, 35
union all select 5, '2012-09-15', '2012-09-16 00:58:24.810', '1-61-1 Hopper term', 467, 35
union all select 6, '2012-09-15', '2012-09-16 00:58:59.537', '1-61-1 Hopper term', 467, 35
union all select 7, '2012-09-15', '2012-09-16 00:59:34.993', '1-61-1 Hopper term', 467, 35
union all select 8, '2012-09-15', '2012-09-16 01:00:11.733', '1-61-1 Hopper term', 467, 10
union all select 9, '2012-09-15', '2012-09-16 04:35:40.443', '1-61-1 Hopper term', 467, 37
union all select 10, '2012-09-15','2012-09-16 04:36:29.643', '1-61-1 Hopper term', 467, 49
union all select 11, '2012-09-15', '2012-09-16 04:37:05.557', '1-61-1 Hopper term',467, 36
union all select 12, '2012-09-15', '2012-09-16 04:37:47.503', '1-61-1 Hopper term', 467, 42
union all select 13, '2012-09-15', '2012-09-16 04:40:17.297', '1-61-1 Hopper term', 467 ,20

union all select 14, '2012-09-15', '2012-09-15 16:38:17.063', '1-61-2 Hopper term', 468, 34
union all select 15, '2012-09-15', '2012-09-15 16:38:51.277', '1-61-2 Hopper term', 468, 35
union all select 16, '2012-09-15', '2012-09-15 16:39:27.327', '1-61-2 Hopper term', 468, 36
union all select 17, '2012-09-15', '2012-09-15 16:40:00.133', '1-61-2 Hopper term', 468, 33
union all select 18, '2012-09-15', '2012-09-15 16:40:34.440', '1-61-2 Hopper term', 468, 34
union all select 19, '2012-09-15', '2012-09-15 16:41:08.727', '1-61-2 Hopper term', 468, 30
union all select 20, '2012-09-15', '2012-09-16 02:31:18.497', '1-61-2 Hopper term', 468, 35
union all select 21, '2012-09-15', '2012-09-16 02:32:22.893', '1-61-2 Hopper term', 468, 64
union all select 22, '2012-09-15', '2012-09-16 02:33:59.333', '1-61-2 Hopper term', 468, 96
union all select 23, '2012-09-15', '2012-09-16 02:34:33.497', '1-61-2 Hopper term', 468, 20
union all select 24, '2012-09-15', '2012-09-16 03:35:37.847', '1-61-2 Hopper term', 468, 64
union all select 25, '2012-09-15', '2012-09-16 03:44:45.237', '1-61-2 Hopper term', 468, 0
union all select 26, '2012-09-15', '2012-09-16 03:45:19.400', '1-61-2 Hopper term', 468, 34
union all select 27, '2012-09-15', '2012-09-16 03:47:44.777', '1-61-2 Hopper term', 468, 10

;with cte_T
as
(select a.row, a.prday, a.btime, b.btime as etime ,a.number , datediff(second,a.btime,b.btime) as cnt
 from T as a left join T as b
 on a.number=b.number
 and a.row = b.row-1
)

, cte_TT
as
( select  row,prday,btime,etime,number,cnt
  from cte_T as A
  where not exists(select 1 from cte_T where number=a.number and cnt<=720 and row=a.row-1)
 union all
  select A1.row,A1.prday,A1.btime,A2.etime,A1.number,A1.cnt
  from cte_TT as A1, cte_T as A2
  where A1.number=A2.number
    and A2.cnt <=720
    and A2.btime=A1.etime
)
,cte_TTT
as
(
select row,prday,btime,max(etime) as etime,number,cnt
from cte_TT
group by row,prday,btime,number,cnt
)

Select A.row, convert(char(10),A.prday,120) as prday, A.btime,
  etime = dateadd(second, (B.seconds), A.etime) 
  ,A.number
from cte_TTT as A
inner join T as B
on A.number=B.number
and A.etime=B.btime

/**
row     prday            btime                          etime            number
---------------------------------------------------------------------------------
1	2012-09-15	2012-09-16 00:55:53.350	2012-09-16 01:00:21.733	467
9	2012-09-15	2012-09-16 04:35:40.443	2012-09-16 04:40:37.297	467
14	2012-09-15	2012-09-15 16:38:17.063	2012-09-15 16:41:38.727	468
20	2012-09-15	2012-09-16 02:31:18.497	2012-09-16 02:34:53.497	468
24	2012-09-15	2012-09-16 03:35:37.847	2012-09-16 03:47:54.777	468
**/

drop table T

[playwarcraft]

Create table T(row  int, prday datetime, btime datetime , name varchar(100), number int, seconds int)
insert into T

          select 1 ,'2012-09-15', '2012-09-16 00:55:53.350', '1-61-1 Hopper term' ,467, 35
union all select 2, '2012-09-15', '2012-09-16 00:56:29.307', '1-61-1 Hopper term', 467, 36
union all select 3, '2012-09-15', '2012-09-16 00:57:14.187', '1-61-1 Hopper term', 467, 45
union all select 4, '2012-09-15', '2012-09-16 00:57:49.507', '1-61-1 Hopper term', 467, 35
union all select 5, '2012-09-15', '2012-09-16 00:58:24.810', '1-61-1 Hopper term', 467, 35
union all select 6, '2012-09-15', '2012-09-16 00:58:59.537', '1-61-1 Hopper term', 467, 35
union all select 7, '2012-09-15', '2012-09-16 00:59:34.993', '1-61-1 Hopper term', 467, 35
union all select 8, '2012-09-15', '2012-09-16 01:00:11.733', '1-61-1 Hopper term', 467, 10
union all select 9, '2012-09-15', '2012-09-16 04:35:40.443', '1-61-1 Hopper term', 467, 37
union all select 10, '2012-09-15','2012-09-16 04:36:29.643', '1-61-1 Hopper term', 467, 49
union all select 11, '2012-09-15', '2012-09-16 04:37:05.557', '1-61-1 Hopper term',467, 36
union all select 12, '2012-09-15', '2012-09-16 04:37:47.503', '1-61-1 Hopper term', 467, 42
union all select 13, '2012-09-15', '2012-09-16 04:40:17.297', '1-61-1 Hopper term', 467 ,20

union all select 14, '2012-09-15', '2012-09-15 16:38:17.063', '1-61-2 Hopper term', 468, 34
union all select 15, '2012-09-15', '2012-09-15 16:38:51.277', '1-61-2 Hopper term', 468, 35
union all select 16, '2012-09-15', '2012-09-15 16:39:27.327', '1-61-2 Hopper term', 468, 36
union all select 17, '2012-09-15', '2012-09-15 16:40:00.133', '1-61-2 Hopper term', 468, 33
union all select 18, '2012-09-15', '2012-09-15 16:40:34.440', '1-61-2 Hopper term', 468, 34
union all select 19, '2012-09-15', '2012-09-15 16:41:08.727', '1-61-2 Hopper term', 468, 30
union all select 20, '2012-09-15', '2012-09-16 02:31:18.497', '1-61-2 Hopper term', 468, 35
union all select 21, '2012-09-15', '2012-09-16 02:32:22.893', '1-61-2 Hopper term', 468, 64
union all select 22, '2012-09-15', '2012-09-16 02:33:59.333', '1-61-2 Hopper term', 468, 96
union all select 23, '2012-09-15', '2012-09-16 02:34:33.497', '1-61-2 Hopper term', 468, 20
union all select 24, '2012-09-15', '2012-09-16 03:35:37.847', '1-61-2 Hopper term', 468, 64
union all select 25, '2012-09-15', '2012-09-16 03:44:45.237', '1-61-2 Hopper term', 468, 0
union all select 26, '2012-09-15', '2012-09-16 03:45:19.400', '1-61-2 Hopper term', 468, 34
union all select 27, '2012-09-15', '2012-09-16 03:47:44.777', '1-61-2 Hopper term', 468, 10

;with cte_T
as
(select a.row, a.prday, a.btime, b.btime as etime ,a.number , datediff(second,a.btime,b.btime) as cnt
 from T as a left join T as b
 on a.number=b.number
 and a.row = b.row-1
)

, cte_TT
as
( select  row,prday,btime,etime,number,cnt
  from cte_T as A
  where not exists(select 1 from cte_T where number=a.number and cnt<=720 and row=a.row-1)
 union all
  select A1.row,A1.prday,A1.btime,A2.etime,A1.number,A1.cnt
  from cte_TT as A1, cte_T as A2
  where A1.number=A2.number
    and A2.cnt <=720
    and A2.btime=A1.etime
)
,cte_TTT
as
(
select row,prday,btime,max(etime) as etime,number,cnt
from cte_TT
group by row,prday,btime,number,cnt
)

Select A.row, new_id = row_number() over (order by A.row), A.btime,
  etime = dateadd(second, (B.seconds), A.etime) 
  ,A.number
from cte_TTT as A
inner join T as B
on A.number=B.number
and A.etime=B.btime

/*
row     new_id     btime                   etime                 number
--------------------------------------------------------------------------
1	1	2012-09-16 00:55:53.350	2012-09-16 01:00:21.733	467
9	2	2012-09-16 04:35:40.443	2012-09-16 04:40:37.297	467
14	3	2012-09-15 16:38:17.063	2012-09-15 16:41:38.727	468
20	4	2012-09-16 02:31:18.497	2012-09-16 02:34:53.497	468
24	5	2012-09-16 03:35:37.847	2012-09-16 03:47:54.777	468
*/

drop table T