再发一帖,求一SQL语句!

smneo 2014-02-24 01:58:25
有点复杂,也不知道直接SQL语句能不能得出想要的结果.先谢过!如下:
表A 有如下字段
ID	CODE	VALUE	TIME

1	3101	13	2014-02-21 10:08:06

2	3101	15	2014-02-21 10:08:41

3	3101	12	2014-02-21 10:08:48

4	3101	13	2014-02-21 10:09:08

5	3101	8	2014-02-21 10:12:57

6	3101	5	2014-02-21 10:13:33

7	3101	12	2014-02-21 10:14:48

8	3101	21	2014-02-21 10:15:21

9	3101	13	2014-02-21 10:16:08

10	3101	14	2014-02-21 10:20:55

11	3101	17	2014-02-21 10:21:22

12	3101	19	2014-02-21 10:21:33

13	3101	13	2014-02-21 10:21:58

14	3101	13	2014-02-21 10:22:08

15	3101	13	2014-02-21 10:22:22

16	3101	12	2014-02-21 10:23:01

17	3101	13	2014-02-21 10:23:12

18	3101	15	2014-02-21 10:23:22

19	3101	13	2014-02-21 10:23:45

20	3101	17	2014-02-21 10:24:08

21	3101	13	2014-02-21 11:08:06

22	3101	13	2014-02-21 11:08:41

23	3101	9	2014-02-21 11:08:44

24	3101	12	2014-02-21 11:08:49

25	3101	13	2014-02-21 11:09:08

26	3101	16	2014-02-21 11:10:01


表A是一个设备所采集的数据.有设备编号,采集时间和当前的值.

现需要一个统计功能,求出该设备每天连续时间内超标的详情(采集数据值连续60秒及以上时间都超标),包括:
设备号,超标过程中的最大值是多少,什么时候开始超标,什么时候超标结束.

计算方法:
1.如果连续60秒VALUE值都是大于10的则记录为超标统计记录;
2.如果连续60秒VALUE有大于10的但是也有小于10的,只要有小于10的就从下一条大于10的开始重新计算(只要有小于10的就算不超标,则从下一条超标的开始计算是否有连续超标),小于10之前的就忽略;
3.如果下一条记录和上一条记录相差时间超过120秒则重新记录为一条新的超标统计记录.
4.超标统计记录中MAX_VALUE为在此超标时间段内VALUE的最大值.

例如表A的超标统计结果如下:
ID	CODE	MAX_VALUE	BEGINTIME		ENDTIME

1	3101	15		2014-02-21 10:08:06	2014-02-21 10:09:08

2	3101	21		2014-02-21 10:17:48	2014-02-21 10:19:08

3	3101	19		2014-02-21 10:20:55	2014-02-21 10:24:08

4	3101	16		2014-02-21 11:08:49	2014-02-21 11:10:01


a.A表中ID=1-4为连续时间超过60秒VALUE值大于10的记录,生成为一条结果记录.结果表中的ID=1的记录;
b.A表中ID=5,6的为非超标记录,被丢弃,忽略;
c.A表中ID=7-9为连续时间超过60秒VALUE值大于10的记录,生成为一条结果记录.结果表中的ID=2的记录;
d.A表中ID=10与前一条记录相差时间超过120秒,重新开始计算.ID=10-20为连续时间超过60秒及以上时间VALUE值大于10的记录,生成为一条结果记录.结果表中的ID=3的记录;
e.A表中ID=21与前一条记录相差时间超过120秒,重新开始计算,至ID=23时,因为连续超标时间不足60秒,则ID=21,22,23都被丢弃,忽略.
f.A表中ID=24-26为连续时间超过60秒VALUE值大于10的记录,生成为一条结果记录.结果表中的ID=4的记录;


一个设备一天中会有多个超标的统计.
例如CODE是3101的设备今天的统计是超标了15次,以及这15次的超标详情,什么时候超,什么时候结束,超的过程中最大值是多少.

现在发的这一贴和原来问的有所简化,原帖:

http://bbs.csdn.net/topics/390713542
http://bbs.csdn.net/topics/390714220

麻烦大家给点思路,SQL最好,先谢过大家了!
...全文
166 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
smneo 2014-02-26
  • 打赏
  • 举报
 回复
引用 7 楼 magician547 的回复:
[quote=引用 6 楼 magician547 的回复:] 

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'Test1')
BEGIN
	TRUNCATE TABLE Test1
END
ELSE
BEGIN
	CREATE TABLE Test1
	(
		ID INT NOT NULL,
		CODE INT NOT NULL,
		VALUE INT NOT NULL,
		[TIME] DATETIME NOT NULL
	)
END
GO
INSERT INTO Test1
SELECT 1,3101,13,'2014-02-21 10:08:06' UNION ALL
SELECT 2,3101,15,'2014-02-21 10:08:41' UNION ALL
SELECT 3,3101,12,'2014-02-21 10:08:48' UNION ALL
SELECT 4,3101,13,'2014-02-21 10:09:08' UNION ALL
SELECT 5,3101,8,'2014-02-21 10:12:57' UNION ALL
SELECT 6,3101,5,'2014-02-21 10:13:33' UNION ALL
SELECT 7,3101,12,'2014-02-21 10:14:48' UNION ALL
SELECT 8,3101,21,'2014-02-21 10:15:21' UNION ALL
SELECT 9,3101,13,'2014-02-21 10:16:08' UNION ALL
SELECT 10,3101,14,'2014-02-21 10:20:55' UNION ALL
SELECT 11,3101,17,'2014-02-21 10:21:22' UNION ALL
SELECT 12,3101,19,'2014-02-21 10:21:33' UNION ALL
SELECT 13,3101,13,'2014-02-21 10:21:58' UNION ALL
SELECT 14,3101,13,'2014-02-21 10:22:08' UNION ALL
SELECT 15,3101,13,'2014-02-21 10:22:22' UNION ALL
SELECT 16,3101,12,'2014-02-21 10:23:01' UNION ALL
SELECT 17,3101,13,'2014-02-21 10:23:12' UNION ALL
SELECT 18,3101,15,'2014-02-21 10:23:22' UNION ALL
SELECT 19,3101,13,'2014-02-21 10:23:45' UNION ALL
SELECT 20,3101,17,'2014-02-21 10:24:08' UNION ALL
SELECT 21,3101,13,'2014-02-21 11:08:06' UNION ALL
SELECT 22,3101,13,'2014-02-21 11:08:41' UNION ALL
SELECT 23,3101,9,'2014-02-21 11:08:44' UNION ALL
SELECT 24,3101,12,'2014-02-21 11:08:49' UNION ALL
SELECT 25,3101,13,'2014-02-21 11:09:08' UNION ALL
SELECT 26,3101,16,'2014-02-21 11:10:01'
GO


WITH T1 AS
(
	SELECT CODE,VALUE,TIME,RN = ROW_NUMBER() OVER (ORDER BY [TIME] ASC)
	FROM Test1
),
T2 AS
(
	SELECT CODE,Value,TIME,RN,RM = ROW_NUMBER() OVER (ORDER BY [TIME] ASC)
	FROM T1
	WHERE Value > 10
),
T3 AS
(
	SELECT CODE,MAX(TIME) AS MAXTIME,MIN(TIME) AS MINTIME,RN-RM AS RN,DATEDIFF(SECOND,MIN(TIME),MAX(TIME)) AS DIFF
	FROM T2
	WHERE Value > 10
	GROUP BY CODE,RN-RM
        HAVING DATEDIFF(SECOND,MIN(TIME),MAX(TIME)) > 60
),



T4 AS
(
	SELECT	A.CODE,
			A.VALUE,
			A.TIME,
			CASE WHEN A.RN-A.RM-CASE WHEN DATEADD(SECOND,-DIFF + 120,B.MAXTIME) > TIME THEN 100000 ELSE 0 END < 0
			THEN A.TIME ELSE MAXTIME END AS MAXTIME,
			A.RN-A.RM-CASE WHEN DATEADD(SECOND,-DIFF + 120,B.MAXTIME) > TIME THEN 100000 ELSE 0 END AS Result
	FROM T2 AS A INNER JOIN T3 AS B ON A.CODE = B.CODE AND A.RN - A.RM = B.RN  AND A.TIME <= B.MAXTIME
)

SELECT ID=ROW_NUMBER() OVER (ORDER BY MIN(TIME)),CODE,MAX(VALUE),MIN(TIME),MAX(MAXTIME)
FROM T4
GROUP BY CODE,Result

/*结果
ID	CODE	(无列名)	(无列名)	(无列名)
1	3101	15	2014-02-21 10:08:06.000	2014-02-21 10:09:08.000
2	3101	21	2014-02-21 10:14:48.000	2014-02-21 10:16:08.000
3	3101	19	2014-02-21 10:20:55.000	2014-02-21 11:08:41.000
4	3101	16	2014-02-21 11:08:49.000	2014-02-21 11:10:01.000
*/
根据条数酌情增加100000的值就可以了
加了个60秒的限制[/quote]
引用 7 楼 magician547 的回复:
[quote=引用 6 楼 magician547 的回复:] 

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'Test1')
BEGIN
	TRUNCATE TABLE Test1
END
ELSE
BEGIN
	CREATE TABLE Test1
	(
		ID INT NOT NULL,
		CODE INT NOT NULL,
		VALUE INT NOT NULL,
		[TIME] DATETIME NOT NULL
	)
END
GO
INSERT INTO Test1
SELECT 1,3101,13,'2014-02-21 10:08:06' UNION ALL
SELECT 2,3101,15,'2014-02-21 10:08:41' UNION ALL
SELECT 3,3101,12,'2014-02-21 10:08:48' UNION ALL
SELECT 4,3101,13,'2014-02-21 10:09:08' UNION ALL
SELECT 5,3101,8,'2014-02-21 10:12:57' UNION ALL
SELECT 6,3101,5,'2014-02-21 10:13:33' UNION ALL
SELECT 7,3101,12,'2014-02-21 10:14:48' UNION ALL
SELECT 8,3101,21,'2014-02-21 10:15:21' UNION ALL
SELECT 9,3101,13,'2014-02-21 10:16:08' UNION ALL
SELECT 10,3101,14,'2014-02-21 10:20:55' UNION ALL
SELECT 11,3101,17,'2014-02-21 10:21:22' UNION ALL
SELECT 12,3101,19,'2014-02-21 10:21:33' UNION ALL
SELECT 13,3101,13,'2014-02-21 10:21:58' UNION ALL
SELECT 14,3101,13,'2014-02-21 10:22:08' UNION ALL
SELECT 15,3101,13,'2014-02-21 10:22:22' UNION ALL
SELECT 16,3101,12,'2014-02-21 10:23:01' UNION ALL
SELECT 17,3101,13,'2014-02-21 10:23:12' UNION ALL
SELECT 18,3101,15,'2014-02-21 10:23:22' UNION ALL
SELECT 19,3101,13,'2014-02-21 10:23:45' UNION ALL
SELECT 20,3101,17,'2014-02-21 10:24:08' UNION ALL
SELECT 21,3101,13,'2014-02-21 11:08:06' UNION ALL
SELECT 22,3101,13,'2014-02-21 11:08:41' UNION ALL
SELECT 23,3101,9,'2014-02-21 11:08:44' UNION ALL
SELECT 24,3101,12,'2014-02-21 11:08:49' UNION ALL
SELECT 25,3101,13,'2014-02-21 11:09:08' UNION ALL
SELECT 26,3101,16,'2014-02-21 11:10:01'
GO


WITH T1 AS
(
	SELECT CODE,VALUE,TIME,RN = ROW_NUMBER() OVER (ORDER BY [TIME] ASC)
	FROM Test1
),
T2 AS
(
	SELECT CODE,Value,TIME,RN,RM = ROW_NUMBER() OVER (ORDER BY [TIME] ASC)
	FROM T1
	WHERE Value > 10
),
T3 AS
(
	SELECT CODE,MAX(TIME) AS MAXTIME,MIN(TIME) AS MINTIME,RN-RM AS RN,DATEDIFF(SECOND,MIN(TIME),MAX(TIME)) AS DIFF
	FROM T2
	WHERE Value > 10
	GROUP BY CODE,RN-RM
        HAVING DATEDIFF(SECOND,MIN(TIME),MAX(TIME)) > 60
),



T4 AS
(
	SELECT	A.CODE,
			A.VALUE,
			A.TIME,
			CASE WHEN A.RN-A.RM-CASE WHEN DATEADD(SECOND,-DIFF + 120,B.MAXTIME) > TIME THEN 100000 ELSE 0 END < 0
			THEN A.TIME ELSE MAXTIME END AS MAXTIME,
			A.RN-A.RM-CASE WHEN DATEADD(SECOND,-DIFF + 120,B.MAXTIME) > TIME THEN 100000 ELSE 0 END AS Result
	FROM T2 AS A INNER JOIN T3 AS B ON A.CODE = B.CODE AND A.RN - A.RM = B.RN  AND A.TIME <= B.MAXTIME
)

SELECT ID=ROW_NUMBER() OVER (ORDER BY MIN(TIME)),CODE,MAX(VALUE),MIN(TIME),MAX(MAXTIME)
FROM T4
GROUP BY CODE,Result

/*结果
ID	CODE	(无列名)	(无列名)	(无列名)
1	3101	15	2014-02-21 10:08:06.000	2014-02-21 10:09:08.000
2	3101	21	2014-02-21 10:14:48.000	2014-02-21 10:16:08.000
3	3101	19	2014-02-21 10:20:55.000	2014-02-21 11:08:41.000
4	3101	16	2014-02-21 11:08:49.000	2014-02-21 11:10:01.000
*/
根据条数酌情增加100000的值就可以了
加了个60秒的限制[/quote] 你的这个算法还是有点问题,我用实际数据测试了一下,有些地方还是有错,例如超标时间为1秒都被统计在内了.但是我通过你的思路,配合程序预加工已经正确计算出来了,非常感谢!
孤独加百列 2014-02-25
  • 打赏
  • 举报
 回复
引用 6 楼 magician547 的回复:

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'Test1')
BEGIN
	TRUNCATE TABLE Test1
END
ELSE
BEGIN
	CREATE TABLE Test1
	(
		ID INT NOT NULL,
		CODE INT NOT NULL,
		VALUE INT NOT NULL,
		[TIME] DATETIME NOT NULL
	)
END
GO
INSERT INTO Test1
SELECT 1,3101,13,'2014-02-21 10:08:06' UNION ALL
SELECT 2,3101,15,'2014-02-21 10:08:41' UNION ALL
SELECT 3,3101,12,'2014-02-21 10:08:48' UNION ALL
SELECT 4,3101,13,'2014-02-21 10:09:08' UNION ALL
SELECT 5,3101,8,'2014-02-21 10:12:57' UNION ALL
SELECT 6,3101,5,'2014-02-21 10:13:33' UNION ALL
SELECT 7,3101,12,'2014-02-21 10:14:48' UNION ALL
SELECT 8,3101,21,'2014-02-21 10:15:21' UNION ALL
SELECT 9,3101,13,'2014-02-21 10:16:08' UNION ALL
SELECT 10,3101,14,'2014-02-21 10:20:55' UNION ALL
SELECT 11,3101,17,'2014-02-21 10:21:22' UNION ALL
SELECT 12,3101,19,'2014-02-21 10:21:33' UNION ALL
SELECT 13,3101,13,'2014-02-21 10:21:58' UNION ALL
SELECT 14,3101,13,'2014-02-21 10:22:08' UNION ALL
SELECT 15,3101,13,'2014-02-21 10:22:22' UNION ALL
SELECT 16,3101,12,'2014-02-21 10:23:01' UNION ALL
SELECT 17,3101,13,'2014-02-21 10:23:12' UNION ALL
SELECT 18,3101,15,'2014-02-21 10:23:22' UNION ALL
SELECT 19,3101,13,'2014-02-21 10:23:45' UNION ALL
SELECT 20,3101,17,'2014-02-21 10:24:08' UNION ALL
SELECT 21,3101,13,'2014-02-21 11:08:06' UNION ALL
SELECT 22,3101,13,'2014-02-21 11:08:41' UNION ALL
SELECT 23,3101,9,'2014-02-21 11:08:44' UNION ALL
SELECT 24,3101,12,'2014-02-21 11:08:49' UNION ALL
SELECT 25,3101,13,'2014-02-21 11:09:08' UNION ALL
SELECT 26,3101,16,'2014-02-21 11:10:01'
GO


WITH T1 AS
(
	SELECT CODE,VALUE,TIME,RN = ROW_NUMBER() OVER (ORDER BY [TIME] ASC)
	FROM Test1
),
T2 AS
(
	SELECT CODE,Value,TIME,RN,RM = ROW_NUMBER() OVER (ORDER BY [TIME] ASC)
	FROM T1
	WHERE Value > 10
),
T3 AS
(
	SELECT CODE,MAX(TIME) AS MAXTIME,MIN(TIME) AS MINTIME,RN-RM AS RN,DATEDIFF(SECOND,MIN(TIME),MAX(TIME)) AS DIFF
	FROM T2
	WHERE Value > 10
	GROUP BY CODE,RN-RM
        HAVING DATEDIFF(SECOND,MIN(TIME),MAX(TIME)) > 60
),



T4 AS
(
	SELECT	A.CODE,
			A.VALUE,
			A.TIME,
			CASE WHEN A.RN-A.RM-CASE WHEN DATEADD(SECOND,-DIFF + 120,B.MAXTIME) > TIME THEN 100000 ELSE 0 END < 0
			THEN A.TIME ELSE MAXTIME END AS MAXTIME,
			A.RN-A.RM-CASE WHEN DATEADD(SECOND,-DIFF + 120,B.MAXTIME) > TIME THEN 100000 ELSE 0 END AS Result
	FROM T2 AS A INNER JOIN T3 AS B ON A.CODE = B.CODE AND A.RN - A.RM = B.RN  AND A.TIME <= B.MAXTIME
)

SELECT ID=ROW_NUMBER() OVER (ORDER BY MIN(TIME)),CODE,MAX(VALUE),MIN(TIME),MAX(MAXTIME)
FROM T4
GROUP BY CODE,Result

/*结果
ID	CODE	(无列名)	(无列名)	(无列名)
1	3101	15	2014-02-21 10:08:06.000	2014-02-21 10:09:08.000
2	3101	21	2014-02-21 10:14:48.000	2014-02-21 10:16:08.000
3	3101	19	2014-02-21 10:20:55.000	2014-02-21 11:08:41.000
4	3101	16	2014-02-21 11:08:49.000	2014-02-21 11:10:01.000
*/
根据条数酌情增加100000的值就可以了
加了个60秒的限制
孤独加百列 2014-02-25
  • 打赏
  • 举报
 回复 

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'Test1')
BEGIN
	TRUNCATE TABLE Test1
END
ELSE
BEGIN
	CREATE TABLE Test1
	(
		ID INT NOT NULL,
		CODE INT NOT NULL,
		VALUE INT NOT NULL,
		[TIME] DATETIME NOT NULL
	)
END
GO
INSERT INTO Test1
SELECT 1,3101,13,'2014-02-21 10:08:06' UNION ALL
SELECT 2,3101,15,'2014-02-21 10:08:41' UNION ALL
SELECT 3,3101,12,'2014-02-21 10:08:48' UNION ALL
SELECT 4,3101,13,'2014-02-21 10:09:08' UNION ALL
SELECT 5,3101,8,'2014-02-21 10:12:57' UNION ALL
SELECT 6,3101,5,'2014-02-21 10:13:33' UNION ALL
SELECT 7,3101,12,'2014-02-21 10:14:48' UNION ALL
SELECT 8,3101,21,'2014-02-21 10:15:21' UNION ALL
SELECT 9,3101,13,'2014-02-21 10:16:08' UNION ALL
SELECT 10,3101,14,'2014-02-21 10:20:55' UNION ALL
SELECT 11,3101,17,'2014-02-21 10:21:22' UNION ALL
SELECT 12,3101,19,'2014-02-21 10:21:33' UNION ALL
SELECT 13,3101,13,'2014-02-21 10:21:58' UNION ALL
SELECT 14,3101,13,'2014-02-21 10:22:08' UNION ALL
SELECT 15,3101,13,'2014-02-21 10:22:22' UNION ALL
SELECT 16,3101,12,'2014-02-21 10:23:01' UNION ALL
SELECT 17,3101,13,'2014-02-21 10:23:12' UNION ALL
SELECT 18,3101,15,'2014-02-21 10:23:22' UNION ALL
SELECT 19,3101,13,'2014-02-21 10:23:45' UNION ALL
SELECT 20,3101,17,'2014-02-21 10:24:08' UNION ALL
SELECT 21,3101,13,'2014-02-21 11:08:06' UNION ALL
SELECT 22,3101,13,'2014-02-21 11:08:41' UNION ALL
SELECT 23,3101,9,'2014-02-21 11:08:44' UNION ALL
SELECT 24,3101,12,'2014-02-21 11:08:49' UNION ALL
SELECT 25,3101,13,'2014-02-21 11:09:08' UNION ALL
SELECT 26,3101,16,'2014-02-21 11:10:01'
GO


WITH T1 AS
(
	SELECT CODE,VALUE,TIME,RN = ROW_NUMBER() OVER (ORDER BY [TIME] ASC)
	FROM Test1
),
T2 AS
(
	SELECT CODE,Value,TIME,RN,RM = ROW_NUMBER() OVER (ORDER BY [TIME] ASC)
	FROM T1
	WHERE Value > 10
),
T3 AS
(
	SELECT CODE,MAX(TIME) AS MAXTIME,MIN(TIME) AS MINTIME,RN-RM AS RN,DATEDIFF(SECOND,MIN(TIME),MAX(TIME)) AS DIFF
	FROM T2
	WHERE Value > 10
	GROUP BY CODE,RN-RM
),



T4 AS
(
	SELECT	A.CODE,
			A.VALUE,
			A.TIME,
			CASE WHEN A.RN-A.RM-CASE WHEN DATEADD(SECOND,-DIFF + 120,B.MAXTIME) > TIME THEN 100000 ELSE 0 END < 0
			THEN A.TIME ELSE MAXTIME END AS MAXTIME,
			A.RN-A.RM-CASE WHEN DATEADD(SECOND,-DIFF + 120,B.MAXTIME) > TIME THEN 100000 ELSE 0 END AS Result
	FROM T2 AS A INNER JOIN T3 AS B ON A.CODE = B.CODE AND A.RN - A.RM = B.RN  AND A.TIME <= B.MAXTIME
)

SELECT ID=ROW_NUMBER() OVER (ORDER BY MIN(TIME)),CODE,MAX(VALUE),MIN(TIME),MAX(MAXTIME)
FROM T4
GROUP BY CODE,Result

/*结果
ID	CODE	(无列名)	(无列名)	(无列名)
1	3101	15	2014-02-21 10:08:06.000	2014-02-21 10:09:08.000
2	3101	21	2014-02-21 10:14:48.000	2014-02-21 10:16:08.000
3	3101	19	2014-02-21 10:20:55.000	2014-02-21 11:08:41.000
4	3101	16	2014-02-21 11:08:49.000	2014-02-21 11:10:01.000
*/
根据条数酌情增加100000的值就可以了
smneo 2014-02-25
  • 打赏
  • 举报
 回复
引用 7 楼 magician547 的回复:
加了个60秒的限制
非常感谢,这个思路很不错!
t101lian 2014-02-24
  • 打赏
  • 举报
 回复
引用 3 楼 smneo 的回复:
先看看还有没有其他更好的办法...谢谢大家了.
如果你的数据库不能再另外建表的话 ，就只能按楼上说的用游标一条条记录检索
chair128 2014-02-24
  • 打赏
  • 举报
 回复
EXCEL表格如下，后面三列为辅助列 （间隔：两次采集时间间隔。用时：连续用时，不符合条件的重新从0开始累加） 

ID	CODE	VALUE	TIME	             间隔_秒  用时_秒  分组
1	3101	13	2014-2-21 10:08:06	0 	0	1
2	3101	15	2014-2-21 10:08:41	35 	35	1
3	3101	12	2014-2-21 10:08:48	7 	42	1
4	3101	13	2014-2-21 10:09:08	20 	62	1
5	3101	8	2014-2-21 10:12:57	229 	0	2
6	3101	5	2014-2-21 10:13:33	36 	0	3
7	3101	12	2014-2-21 10:14:48	75 	0	4
8	3101	21	2014-2-21 10:15:21	33 	33	4
9	3101	13	2014-2-21 10:16:08	47 	80	4
10	3101	14	2014-2-21 10:20:55	287 	0	5
11	3101	17	2014-2-21 10:21:22	27 	27	5
12	3101	19	2014-2-21 10:21:33	11 	38	5
13	3101	13	2014-2-21 10:21:58	25 	63	5
14	3101	13	2014-2-21 10:22:08	10 	0	6
15	3101	13	2014-2-21 10:22:22	14 	14	6
16	3101	12	2014-2-21 10:23:01	39 	53	6
17	3101	13	2014-2-21 10:23:12	11 	64	6
18	3101	15	2014-2-21 10:23:22	10 	0	7
19	3101	13	2014-2-21 10:23:45	23 	23	7
20	3101	17	2014-2-21 10:24:08	23 	46	7
21	3101	13	2014-2-21 11:08:06	2638 	0	8
22	3101	13	2014-2-21 11:08:41	35 	35	8
23	3101	9	2014-2-21 11:08:44	3 	0	9
24	3101	12	2014-2-21 11:08:49	5 	5	9
25	3101	13	2014-2-21 11:09:08	19 	24	9
26	3101	16	2014-2-21 11:10:01	53 	77	9
参考VBA代码如下： 

Sub Test()
    Dim cell As Range
    Dim rng As Range
    Dim s As Long
    Dim g As Long
    
    Set rng = Range("E2:E27")
    
    For Each cell In rng
        s = s + Val(cell.Value)
        
        If (Val(cell.Offset(0, -2)) < 10 _
            Or Val(cell.Value) >= 60) _
            Or cell.Offset(-1, 1) >= 60 Then  ' 如果VALUE小于10，或间隔时间大于等于60秒，或连续用时大于等于60秒
            
            s = 0
            g = g + 1
        End If
            
        cell.Offset(0, 1) = s
        cell.Offset(0, 2) = g
    Next cell
End Sub
如用SQL来写，则可用游标，参考以上思路，希望对你有帮助。
smneo 2014-02-24
  • 打赏
  • 举报
 回复
先看看还有没有其他更好的办法...谢谢大家了.
Blessed_Chuan 2014-02-24
  • 打赏
  • 举报
 回复
可以先从第一条记录的时间开始算+60秒 看看是不是大于第二条记录 大于则先看值是不是大于10 如果大于则进行对比value值 保存最大值， 再用第一条记录+60秒和第三条记录比 如果小于第三条时间 大于则先看值是不是大于10 如果大于则进行对比value值 保存最大值 依次类推 使用游标进行操作
KeepSayingNo 2014-02-24
  • 打赏
  • 举报
 回复
你这种就只能用游标一条条记录检索了，然后弄个临时表，这个表的字段有：ID CODE VALUE BEGINTIME ENDTIME 把第一条记录放到临时表中，然后时间填到BEGINTIME一栏，继续遍历表A，如果该条记录的时间和临时表中ENDTIME为空的记录的BEGINTIME的时间间隔大于60秒，则把这条记录的时间填到临时表中ENDTIME为空的记录。再将下一条记录放到临时表中，再继续往下找，以此类推。最后就得到你要的结果。
Oracle中PL/SQL编程对系统性能的影响
C/S结构的工作过程是：当客户端输入并发送一条SQL语句后，便通过网络送到服务器端，在那里被分析执行然后再将结果通过网络返回到客户端，此时客户端可以再发下一条SQL语句。可见客户端是一条语句一条语句的发送，服务器端是一条一条的分析执行，结果再一次次的返回。但是，这样单个的SQL语句引起了频繁的网络通信，大大降低了系统性能。PL/SQL是扩展SQL后的语言， PL/SQL块作为一个单位发送，使用了较少的网络通信，而且在运行中能根据条件，决定执行或重复执行什么语句，既保留了SQL的强大性，又弥补了不足。
再发C#初学者SQL联合查询实例
摘要:C#源码,菜单窗体,dataGridView　　本站已经分享过C#与SQL的联合查询实例了，这一个也是挺不错的，通过union语句，将高考成绩表中总成绩大于500的考生与高考学生信息表中籍贯为中国北京的考生信息，一起显示在显示出来。数据库测试数据在Database文件夹中，测试前请自行附加哦。
7天带你玩转Mysql数据库之SQL语句
您观看课程学习后 免费入群领取【超全Python资料包+17本学习电子书】 七年SQL语句编写经验。满满的干货！ 在网上找了好多SQL数据库的教程，但你还会面临这样的问题： 1. 自己写了SQL却不知道对不对。 2. 在上线后...
SQL语句练习实例之五 WMS系统中的关于LIFO或FIFO的问题分析
代码如下: —在仓储管理中经常会碰到的一个问题 一、关于LIFO与FIFO的简单说明 —FIFO: First in, First out.先进先出。 —LIFO: Last in, First out.后进先出。 –如货物A：本月1日购买10件，单价10元/件，3日购买20件，单价15元/件；10日购买10件，单价8元/件。 –本月15日发货35件。 –按FIFO先进先出，就是先购入的存货先发出，所以，先发1日进货的10件，再发3日进货的20件，最后发10日进货的5件，发出成本共为：10*10+20*15+5*8=440元。 –按LIFO后进先出，就是后购入的存货先发出，所以，先发10日进货
ajax真分页实例(J2ee)
本案例使用ajax技术实现分页，方案为其中一种，其他方案后面会再发，因为项目比较有针对性，所以项目规模很小，便于查看学习，使用前先修改项目中的Constant类中的相关静态变量信息（数据库连接信息）后便可部署。往上翻页漏了一条获取单选框值的语句，按照往下翻页填上去就可以了,忘记放SQL文件了，需要的可以去http://kk3173603.iteye.com/blog/997724 这个地址下载。
疑难问题

22,210

社区成员

121,729

社区内容

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

加载中

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

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

+ 用AI写文章