论坛首页 精选版块
移动开发
iOSAndroidQtWP
云计算
IaaSPass/SaaS分布式计算/Hadoop
Java技术
Java SEJava Web 开发Java EEJava其他相关
.NET技术
.NET FrameworkC#.NET分析与设计ASP .NETVB .NET
Web开发
PHPJavaScriptASPHTML(CSS)HTML5Apache
开发语言/框架
DelphiVC/MFCVBC/C++C++ Builder其他开发语言
数据库开发
MS-SQL ServerOraclePowerBuilderInformatica其他数据库开发
硬件/嵌入式开发
嵌入开发(WinCE)驱动开发/核心开发硬件设计单片机/工控汇编语言VxWorks开发
Linux/Unix社区
系统维护与使用区应用程序开发区内核源代码研究区驱动程序开发区CPU和硬件区
 论坛牛人 排行榜 论坛地图 我要发贴 APP发帖积分减半，结帖积分翻倍
论坛帮助
CSDN论坛 > MS-SQL Server > 疑难问题

统计监测异常情况的开始与恢复时间段 [问题点数：100分，结帖人CruelYoung123]

更多帖子
空间 博客
CruelYoung123
Bbs1
本版专家分：2
结帖率 100%
如下所示：

数据库状态为：

时间                    状态
2013-10-20 00:01:50 200
2013-10-20 00:04:45  200
2013-10-20 00:08:27  200
2013-10-20 00:11:17 200
2013-10-20 00:14:07  200
2013-10-20 00:17:04 400
2013-10-20 00:21:15 400
2013-10-20 00:24:15  400
2013-10-20 00:28:29   400
2013-10-20 00:32:21   200
2013-10-20 00:35:10   200
2013-10-20 00:38:51 200
2013-10-20 00:41:40   400
2013-10-20 00:44:30   200
2013-10-20 00:47:22  200
2013-10-20 00:50:46 200
2013-10-20 00:54:19   200
2013-10-20 00:58:12  200
2013-10-20 01:01:02   200
2013-10-20 01:03:55   200
2013-10-20 01:08:15   200
2013-10-20 01:11:39   200
2013-10-20 01:15:19   200
2013-10-20 01:20:11   200
2013-10-20 01:24:10  200
2013-10-20 01:28:08  200
2013-10-20 01:32:04    200
2013-10-20 01:34:55 200
2013-10-20 01:38:16 200
2013-10-20 01:41:21  200
2013-10-20 01:45:21  200
2013-10-20 01:51:27 200
2013-10-20 01:56:31  200
2013-10-20 02:03:00   200
2013-10-20 02:08:01  200
2013-10-20 02:13:40 200
2013-10-20 02:18:37  200
2013-10-20 02:23:38  200
2013-10-20 02:30:06 200
2013-10-20 02:33:55   200
2013-10-20 02:37:44  200
2013-10-20 02:41:41  200
2013-10-20 02:45:35  200
2013-10-20 02:51:04 200
2013-10-20 02:55:13 200
2013-10-20 02:59:24  200
2013-10-20 03:03:15   200
2013-10-20 03:07:04 200
2013-10-20 03:11:25   1001
2013-10-20 03:16:20 1001
2013-10-20 03:21:42  200
2013-10-20 03:25:32   200
2013-10-20 03:29:23  200
2013-10-20 03:33:15 200
2013-10-20 03:38:19  200
2013-10-20 03:42:09  200
2013-10-20 03:45:59 200
2013-10-20 03:50:25  200
2013-10-20 03:54:15   200
2013-10-20 03:58:17  200
2013-10-20 04:02:07 1003
2013-10-20 04:06:00  200
2013-10-20 04:11:20 200
2013-10-20 04:16:14  200
2013-10-20 04:20:57  200
2013-10-20 04:24:48  200
2013-10-20 04:28:47  200
2013-10-20 04:32:39 200



以上状态200为正常情况， 需要统计不正常的开始时间及恢复正常的时间，以及不正常的持续时间间隔，希望用SQL语句得到如下自动化的结果。
序号      开始时间             恢复时间          时间间隔  
1
回复数 13 只看楼主 引用 举报 楼主
更多帖子
空间 博客
Andy__Huang
Bbs9
本版专家分：67868
Blank
铜牌 2013年9月 总版技术专家分月排行榜第三
Blank
红花 2013年7月 MS-SQL Server大版内专家分月排行榜第一
2009年3月 Oracle大版内专家分月排行榜第一
2009年2月 Oracle大版内专家分月排行榜第一
Blank
黄花 2013年9月 MS-SQL Server大版内专家分月排行榜第二
2013年8月 MS-SQL Server大版内专家分月排行榜第二
2009年4月 Oracle大版内专家分月排行榜第二
2009年1月 Oracle大版内专家分月排行榜第二
2004年10月 MS-SQL Server大版内专家分月排行榜第二
Blank
蓝花 2013年10月 MS-SQL Server大版内专家分月排行榜第三
2008年12月 Oracle大版内专家分月排行榜第三
2004年11月 MS-SQL Server大版内专家分月排行榜第三 
;with cte(时间,状态) as

(

select '2013-10-20 00:01:50',200

union all select '2013-10-20 00:04:45',200

union all select '2013-10-20 00:08:27',200

union all select '2013-10-20 00:11:17',200

union all select '2013-10-20 00:14:07',200

union all select '2013-10-20 00:17:04',400

union all select '2013-10-20 00:21:15',400

union all select '2013-10-20 00:24:15',400

union all select '2013-10-20 00:28:29',400

union all select '2013-10-20 00:32:21',200

union all select '2013-10-20 00:35:10',200

union all select '2013-10-20 00:38:51',200

union all select '2013-10-20 00:41:40',400

union all select '2013-10-20 00:44:30',200

union all select '2013-10-20 00:47:22',200

union all select '2013-10-20 00:50:46',200

union all select '2013-10-20 00:54:19',200

union all select '2013-10-20 00:58:12',200

union all select '2013-10-20 01:01:02',200

union all select '2013-10-20 01:03:55',200

union all select '2013-10-20 01:08:15',200

union all select '2013-10-20 01:11:39',200

union all select '2013-10-20 01:15:19',200

union all select '2013-10-20 01:20:11',200

union all select '2013-10-20 01:24:10',200

union all select '2013-10-20 01:28:08',200

union all select '2013-10-20 01:32:04',200

union all select '2013-10-20 01:34:55',200

union all select '2013-10-20 01:38:16',200

union all select '2013-10-20 01:41:21',200

union all select '2013-10-20 01:45:21',200

union all select '2013-10-20 01:51:27',200

union all select '2013-10-20 01:56:31',200

union all select '2013-10-20 02:03:00',200

union all select '2013-10-20 02:08:01',200

union all select '2013-10-20 02:13:40',200

union all select '2013-10-20 02:18:37',200

union all select '2013-10-20 02:23:38',200

union all select '2013-10-20 02:30:06',200

union all select '2013-10-20 02:33:55',200

union all select '2013-10-20 02:37:44',200

union all select '2013-10-20 02:41:41',200

union all select '2013-10-20 02:45:35',200

union all select '2013-10-20 02:51:04',200

union all select '2013-10-20 02:55:13',200

union all select '2013-10-20 02:59:24',200

union all select '2013-10-20 03:03:15',200

union all select '2013-10-20 03:07:04',200

union all select '2013-10-20 03:11:25',1001

union all select '2013-10-20 03:16:20',1001

union all select '2013-10-20 03:21:42',200

union all select '2013-10-20 03:25:32',200

union all select '2013-10-20 03:29:23',200

union all select '2013-10-20 03:33:15',200

union all select '2013-10-20 03:38:19',200

union all select '2013-10-20 03:42:09',200

union all select '2013-10-20 03:45:59',200

union all select '2013-10-20 03:50:25',200

union all select '2013-10-20 03:54:15',200

union all select '2013-10-20 03:58:17',200

union all select '2013-10-20 04:02:07',1003

union all select '2013-10-20 04:06:00',200

union all select '2013-10-20 04:11:20',200

union all select '2013-10-20 04:16:14',200

union all select '2013-10-20 04:20:57',200

union all select '2013-10-20 04:24:48',200

union all select '2013-10-20 04:28:47',200

union all select '2013-10-20 04:32:39',200

)

select 序号=ROW_NUMBER() over(order by 时间),

	开始时间=时间,恢复时间=时间2,时间间隔=DATEDIFF(minute,时间,时间2)

from 

(

select *,时间2=(select top 1 时间 from cte b where b.时间>a.时间 and b.状态=200)

from cte a

where 状态<>200

)t



/*

序号	开始时间	恢复时间	时间间隔

1	2013-10-20 00:17:04	2013-10-20 00:32:21	15

2	2013-10-20 00:21:15	2013-10-20 00:32:21	11

3	2013-10-20 00:24:15	2013-10-20 00:32:21	8

4	2013-10-20 00:28:29	2013-10-20 00:32:21	4

5	2013-10-20 00:41:40	2013-10-20 00:44:30	3

6	2013-10-20 03:11:25	2013-10-20 03:21:42	10

7	2013-10-20 03:16:20	2013-10-20 03:21:42	5

8	2013-10-20 04:02:07	2013-10-20 04:06:00	4

*/
只看TA 引用 举报 #1    得分 43
更多帖子
空间 博客
CruelYoung123
Bbs1
本版专家分：2
谢谢楼上。。。但是。。。在数据库中数据是海量的。。。

那个union select怎么操作哈？
只看TA 引用 举报 #2    得分 0
更多帖子
空间 博客
發糞塗牆
Bbs11
本版专家分：214398
Blank
状元 2014年 总版技术专家分年内排行榜第一
Blank
榜眼 2013年 总版技术专家分年内排行榜第二
Blank
金牌 2014年8月 总版技术专家分月排行榜第一
2014年7月 总版技术专家分月排行榜第一
2014年6月 总版技术专家分月排行榜第一
2014年5月 总版技术专家分月排行榜第一
2014年4月 总版技术专家分月排行榜第一
2014年3月 总版技术专家分月排行榜第一
2014年1月 总版技术专家分月排行榜第一
2013年12月 总版技术专家分月排行榜第一
Blank
银牌 2013年10月 总版技术专家分月排行榜第二
2012年11月 总版技术专家分月排行榜第二
他只是造数据，看下面的select就可以了
只看TA 引用 举报 #3    得分 5
更多帖子
空间 博客
Andy__Huang
Bbs9
本版专家分：67868
Blank
铜牌 2013年9月 总版技术专家分月排行榜第三
Blank
红花 2013年7月 MS-SQL Server大版内专家分月排行榜第一
2009年3月 Oracle大版内专家分月排行榜第一
2009年2月 Oracle大版内专家分月排行榜第一
Blank
黄花 2013年9月 MS-SQL Server大版内专家分月排行榜第二
2013年8月 MS-SQL Server大版内专家分月排行榜第二
2009年4月 Oracle大版内专家分月排行榜第二
2009年1月 Oracle大版内专家分月排行榜第二
2004年10月 MS-SQL Server大版内专家分月排行榜第二
Blank
蓝花 2013年10月 MS-SQL Server大版内专家分月排行榜第三
2008年12月 Oracle大版内专家分月排行榜第三
2004年11月 MS-SQL Server大版内专家分月排行榜第三
你的表不是已经存储有数据了吗，直接从你的表里面select出来就可以，也就用句这个语句就可以了

select 序号=ROW_NUMBER() over(order by 时间),

	开始时间=时间,恢复时间=时间2,时间间隔=DATEDIFF(minute,时间,时间2)

from 

(

select *,时间2=(select top 1 时间 from [你的表] b where b.时间>a.时间 and b.状态=200)

from [你的表] a

where 状态<>200

)t
只看TA 引用 举报 #4    得分 0
更多帖子
空间 博客
ai_li7758521
Bbs7
本版专家分：22938
Blank
红花 2009年6月 MS-SQL Server大版内专家分月排行榜第一 
WITH CTE(时间,状态)

AS

(

 select '2013-10-20 00:01:50',200 union all 

 select '2013-10-20 00:04:45',200 union all 

 select '2013-10-20 00:08:27',200 union all 

 select '2013-10-20 00:11:17',200 union all 

 select '2013-10-20 00:14:07',200 union all 

 select '2013-10-20 00:17:04',400 union all 

 select '2013-10-20 00:21:15',400 union all 

 select '2013-10-20 00:24:15',400 union all 

 select '2013-10-20 00:28:29',400 union all 

 select '2013-10-20 00:32:21',200 union all 

 select '2013-10-20 00:35:10',200 union all 

 select '2013-10-20 00:38:51',200 union all 

 select '2013-10-20 00:41:40',400 union all 

 select '2013-10-20 00:44:30',200 union all 

 select '2013-10-20 00:47:22',200 union all 

 select '2013-10-20 00:50:46',200 union all 

 select '2013-10-20 00:54:19',200 union all 

 select '2013-10-20 00:58:12',200 union all 

 select '2013-10-20 01:01:02',200 union all 

 select '2013-10-20 01:03:55',200 union all 

 select '2013-10-20 01:08:15',200 union all 

 select '2013-10-20 01:11:39',200 union all 

 select '2013-10-20 01:15:19',200 union all 

 select '2013-10-20 01:20:11',200 union all 

 select '2013-10-20 01:24:10',200 union all 

 select '2013-10-20 01:28:08',200 union all 

 select '2013-10-20 01:32:04',200 union all 

 select '2013-10-20 01:34:55',200 union all 

 select '2013-10-20 01:38:16',200 union all 

 select '2013-10-20 01:41:21',200 union all 

 select '2013-10-20 01:45:21',200 union all 

 select '2013-10-20 01:51:27',200 union all 

 select '2013-10-20 01:56:31',200 union all 

 select '2013-10-20 02:03:00',200 union all 

 select '2013-10-20 02:08:01',200 union all 

 select '2013-10-20 02:13:40',200 union all 

 select '2013-10-20 02:18:37',200 union all 

 select '2013-10-20 02:23:38',200 union all 

 select '2013-10-20 02:30:06',200 union all 

 select '2013-10-20 02:33:55',200 union all 

 select '2013-10-20 02:37:44',200 union all 

 select '2013-10-20 02:41:41',200 union all 

 select '2013-10-20 02:45:35',200 union all 

 select '2013-10-20 02:51:04',200 union all 

 select '2013-10-20 02:55:13',200 union all 

 select '2013-10-20 02:59:24',200 union all 

 select '2013-10-20 03:03:15',200 union all 

 select '2013-10-20 03:07:04',200 union all 

 select '2013-10-20 03:11:25',1001 union all 

 select '2013-10-20 03:16:20',1001 union all 

 select '2013-10-20 03:21:42',200 union all 

 select '2013-10-20 03:25:32',200 union all 

 select '2013-10-20 03:29:23',200 union all 

 select '2013-10-20 03:33:15',200 union all 

 select '2013-10-20 03:38:19',200 union all 

 select '2013-10-20 03:42:09',200 union all 

 select '2013-10-20 03:45:59',200 union all 

 select '2013-10-20 03:50:25',200 union all 

 select '2013-10-20 03:54:15',200 union all 

 select '2013-10-20 03:58:17',200 union all 

 select '2013-10-20 04:02:07',1003 union all 

 select '2013-10-20 04:06:00',200 union all 

 select '2013-10-20 04:11:20',200 union all 

 select '2013-10-20 04:16:14',200 union all 

 select '2013-10-20 04:20:57',200 union all 

 select '2013-10-20 04:24:48',200 union all 

 select '2013-10-20 04:28:47',200 union all 

 select '2013-10-20 04:32:39',200

),

C1 AS

(

	SELECT G=ROW_NUMBER() OVER(ORDER BY [时间])-RANK() OVER(PARTITION BY [状态] ORDER BY [时间]),*

	FROM CTE

),

C2 AS

(

	SELECT 开始时间=MIN([时间]),恢复时间=MAX([时间]),时间间隔 =DATEDIFF(SECOND,MIN([时间]),MAX([时间]))

	FROM C1

	WHERE 状态!=200

	GROUP BY G

)



SELECT 序号=ROW_NUMBER() OVER(ORDER BY 开始时间),*

FROM C2



--序号	开始时间	恢复时间	时间间隔

--1	2013-10-20 00:17:04	2013-10-20 00:28:29	685

--2	2013-10-20 00:41:40	2013-10-20 00:41:40	0

--3	2013-10-20 03:11:25	2013-10-20 03:16:20	295

--4	2013-10-20 04:02:07	2013-10-20 04:02:07	0
只看TA 引用 举报 #5    得分 10
更多帖子
空间 博客
ai_li7758521
Bbs7
本版专家分：22938
Blank
红花 2009年6月 MS-SQL Server大版内专家分月排行榜第一
引用 2 楼 CruelYoung123 的回复:
谢谢楼上。。。但是。。。在数据库中数据是海量的。。。

那个union select怎么操作哈？


其中CTE就相当于你自己的数据表。用你的数据表名替换CTE即可。
只看TA 引用 举报 #6    得分 0
更多帖子
空间 博客
CruelYoung123
Bbs1
本版专家分：2
这个语句还是有一些问题。。。连接异常的应该只报一次，如

这个结果。。第一条应该是：
1    2013-10-20 00:17:04    2013-10-20 00:32:21    15

2.3.4条其实就是算一次异常时间问题。
只看TA 引用 举报 #7    得分 0
更多帖子
空间 博客
不想长大啊
Bbs9
本版专家分：84179
版主
Blank
金牌 2014年2月 总版技术专家分月排行榜第一
Blank
优秀版主 2016年10月优秀小版主
Blank
银牌 2014年1月 总版技术专家分月排行榜第二
2013年12月 总版技术专家分月排行榜第二
Blank
微软MVP 2014年4月 荣获微软MVP称号
引用 7 楼 CruelYoung123 的回复:
这个语句还是有一些问题。。。连接异常的应该只报一次，如

这个结果。。第一条应该是：
1    2013-10-20 00:17:04    2013-10-20 00:32:21    15

2.3.4条其实就是算一次异常时间问题。


后面3条是一条，这个怎么定义呢
只看TA 引用 举报 #8    得分 2
更多帖子
空间 博客
CruelYoung123
Bbs1
本版专家分：2
序号    开始时间    恢复时间    时间间隔
1    2013-10-20 00:17:04    2013-10-20 00:32:21    15
2    2013-10-20 00:21:15    2013-10-20 00:32:21    11
3    2013-10-20 00:24:15    2013-10-20 00:32:21    8
4    2013-10-20 00:28:29    2013-10-20 00:32:21    4

这个4条记录恢复时间都是2013-10-20 00:32:21， 这里重复四条。。
其实异常时间段就是
序号    开始时间    恢复时间    时间间隔
1    2013-10-20 00:17:04    2013-10-20 00:32:21    15

后面2.3.4 是多余重复统计， 也是不正确的。。。
只看TA 引用 举报 #9    得分 0
更多帖子
空间 博客
CruelYoung123
Bbs1
本版专家分：2
还请各位帮忙解决一下。。。
2013-10-20 00:17:04  到  2013-10-20 00:32:21只算一次异常状态
只看TA 引用 举报 #10    得分 0
更多帖子
空间 博客
Leon_He2014
Bbs5
本版专家分：4628


select '2013-10-20 00:01:50' as statetime,200 stateid

into #temp

union all select '2013-10-20 00:04:45',200

union all select '2013-10-20 00:08:27',200

union all select '2013-10-20 00:11:17',200

union all select '2013-10-20 00:14:07',200

union all select '2013-10-20 00:17:04',400

union all select '2013-10-20 00:21:15',400

union all select '2013-10-20 00:24:15',400

union all select '2013-10-20 00:28:29',400

union all select '2013-10-20 00:32:21',200

union all select '2013-10-20 00:35:10',200

union all select '2013-10-20 00:38:51',200

union all select '2013-10-20 00:41:40',400

union all select '2013-10-20 00:44:30',200

union all select '2013-10-20 00:47:22',200

union all select '2013-10-20 00:50:46',200

union all select '2013-10-20 00:54:19',200

union all select '2013-10-20 00:58:12',200

union all select '2013-10-20 01:01:02',200

union all select '2013-10-20 01:03:55',200

union all select '2013-10-20 01:08:15',200

union all select '2013-10-20 01:11:39',200

union all select '2013-10-20 01:15:19',200

union all select '2013-10-20 01:20:11',200

union all select '2013-10-20 01:24:10',200

union all select '2013-10-20 01:28:08',200

union all select '2013-10-20 01:32:04',200

union all select '2013-10-20 01:34:55',200

union all select '2013-10-20 01:38:16',200

union all select '2013-10-20 01:41:21',200

union all select '2013-10-20 01:45:21',200

union all select '2013-10-20 01:51:27',200

union all select '2013-10-20 01:56:31',200

union all select '2013-10-20 02:03:00',200

union all select '2013-10-20 02:08:01',200

union all select '2013-10-20 02:13:40',200

union all select '2013-10-20 02:18:37',200

union all select '2013-10-20 02:23:38',200

union all select '2013-10-20 02:30:06',200

union all select '2013-10-20 02:33:55',200

union all select '2013-10-20 02:37:44',200

union all select '2013-10-20 02:41:41',200

union all select '2013-10-20 02:45:35',200

union all select '2013-10-20 02:51:04',200

union all select '2013-10-20 02:55:13',200

union all select '2013-10-20 02:59:24',200

union all select '2013-10-20 03:03:15',200

union all select '2013-10-20 03:07:04',200

union all select '2013-10-20 03:11:25',1001

union all select '2013-10-20 03:16:20',1001

union all select '2013-10-20 03:21:42',200

union all select '2013-10-20 03:25:32',200

union all select '2013-10-20 03:29:23',200

union all select '2013-10-20 03:33:15',200

union all select '2013-10-20 03:38:19',200

union all select '2013-10-20 03:42:09',200

union all select '2013-10-20 03:45:59',200

union all select '2013-10-20 03:50:25',200

union all select '2013-10-20 03:54:15',200

union all select '2013-10-20 03:58:17',200

union all select '2013-10-20 04:02:07',1003

union all select '2013-10-20 04:06:00',200

union all select '2013-10-20 04:11:20',200

union all select '2013-10-20 04:16:14',200

union all select '2013-10-20 04:20:57',200

union all select '2013-10-20 04:24:48',200

union all select '2013-10-20 04:28:47',200

union all select '2013-10-20 04:32:39',200



select ROW_NUMBER() over(order by gptime) id ,min(statetime) statetime,gptime endtime

,DATEDIFF(minute,min(statetime),gptime) timediff

from

(

select *

,(select min(statetime) from #temp b where b.statetime>=a.statetime and b.stateid=200) gptime

from #temp a

) t

where stateid<>200

group by gptime



/*

id	statetime	endtime	timediff

1	2013-10-20 00:17:04	2013-10-20 00:32:21	15

2	2013-10-20 00:41:40	2013-10-20 00:44:30	3

3	2013-10-20 03:11:25	2013-10-20 03:21:42	10

4	2013-10-20 04:02:07	2013-10-20 04:06:00	4

*/
只看TA 引用 举报 #11    得分 40
CruelYoung123
Bbs1
本版专家分：2
非常感谢。。。。各位
只看TA 引用 举报 #12    得分 0