T-sql求一个解法

nettt 2012-11-09 10:09:22
这是一个的算法问题
要处理的是计算设备停机的一笔数据。
我学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
...全文
149 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
playwarcraft 2012-11-16
  • 打赏
  • 举报
 回复 

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
playwarcraft 2012-11-16
  • 打赏
  • 举报
 回复 

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
nettt 2012-11-16
  • 打赏
  • 举报
 回复
求解。。。。。。。。。。。。。
nettt 2012-11-12
  • 打赏
  • 举报
 回复
row_,是连续的，是自增的，prday是日期，第天都不一样，
快溜 2012-11-09
  • 打赏
  • 举报
 回复
嗯，问题写的很详细。row_ prday是连续的吗
Microsoft SQL Server 2005技术内幕： T-SQ程序设计.pdf
书中充满了此种结合不同技术的意想不到之解法，让懂得关键之人顿感妙趣横生，不懂奥妙之人，仍可借鉴引用。
sql sever 使用T-SQL编程
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录一、信息打印二、变量1.三、运算符四、流程控制 提示：以下是本篇文章正文内容，下面案例可供参考 一、信息打印 二、变量 1. 三、运算符 四、流程控制 ...
sql server系列课程t-sql基础语法篇
本课程主要讲解标识符，变量，常量，运算符，表达式，流程控制语句，链接查询，where条件，order by ，group by ，having by 以及关联查询等知识点的详解。
书评 -- Inside Microsoft SQL Server 2005 : T-SQL Querying，T-SQL Programming
随着 SQL Server 版本的演进，T-SQL(Transact Structure Query Language)变得独立而功能强大，且拥有众多使用者，是解决各种数据问题的主流语言。由于公司内各种数据日益庞大、MS SQL Server 的功能扩增、其产品广泛进入企业各系统，让专门处理数据的 T-SQL 语言变成显学。在关系数据库中，以集合(set)的方式来处理大量纪录才有效率，使得 S
T-SQL 分割字符串问题汇总
  T-SQL 分割字符串问题 1) declare @string varchar(1000)declare @xh char(10)declare @sh varchar(13)set @string=l,ll,xl,ml,xxlset @string=select * into ##tmp from (select +replace(@string,,,
疑难问题

22,209

社区成员

121,731

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章