不用函数,如何用一条SQL语句实现一个功能,SQL高手请进.

表及其内容见下
ID OBJ_ID STAMP
----------------------------------------
1 1000 2006-10-10 11:01:06.000
2 1000 2006-10-10 11:01:08.000
3 1000 2006-10-10 11:01:09.000
4 1000 2006-10-10 11:01:10.000
5 1000 2006-10-10 11:01:13.000
6 2000 2006-10-10 11:02:06.000
7 2000 2006-10-10 11:02:09.000
8 2000 2006-10-10 11:02:15.000
9 2000 2006-10-10 11:02:16.000
10 2000 2006-10-10 11:02:24.000
11 2000 2006-10-10 11:02:31.000
12 3000 2006-10-10 11:00:06.000
13 3000 2006-10-10 11:01:06.000
14 3000 2006-10-10 11:02:06.000
15 3000 2006-10-10 11:03:06.000
16 3000 2006-10-10 11:03:26.000
17 4000 2006-10-10 10:01:15.000
18 4000 2006-10-10 10:01:20.000
19 4000 2006-10-10 10:01:21.000
20 4000 2006-10-10 10:01:23.000
21 4000 2006-10-10 10:01:25.000
22 4000 2006-10-10 10:01:26.000

如何查找出同一个OBJ_ID在前后时间不超过5秒,且出现了三次以上的间隔不超过5秒的OBJ_ID.如上面,OBJ_ID为1000的,出现了5次,且其按照排序后,前后一条数据都不超过5秒,计前后间隔不超过5秒的有4次,所以OBJ_ID为1000的符合选择的要求.OBJ_ID为2000的,出现了6次,但其按照时间排序后,前后一条数据不超过5秒的,只出现了两次,分别为
2006-10-10 11:02:06.000 到 2006-10-10 11:02:09.000 及
2006-10-10 11:02:15.000 到 2006-10-10 11:02:16.000
所以OBJ_ID为2000的不符合选择的要求.

下面的OBJ_ID为3000的不符合选择的要求,OBJ_ID为4000的符合选择的要求.

如何用一条SQL语句把上面符合要求的OBJ_ID(1000,4000)给找出来?
如果用ORACLE的话,是可以选出来的,思想为选择的时候新加一个列,列值为后面的一列减去前面的一列,得出一个时间的差值.有了这个时间的差值,就可以和目的数据(5秒)进行比较,这样就可以统计出现了几次了.

附建表语句
CREATE TABLE [t_temp] (
[id] [decimal](18, 0) NULL ,
[obj_id] [decimal](18, 0) NULL ,
[stamp] [datetime] NULL
) ON [PRIMARY]
GO

insert into t_temp values(1,1000,'2006-10-10 11:01:06.000')
insert into t_temp values(2,1000,'2006-10-10 11:01:08.000')
insert into t_temp values(3,1000,'2006-10-10 11:01:09.000')
insert into t_temp values(4,1000,'2006-10-10 11:01:10.000')
insert into t_temp values(5,1000,'2006-10-10 11:01:13.000')
insert into t_temp values(6,2000,'2006-10-10 11:02:06.000')
insert into t_temp values(7,2000,'2006-10-10 11:02:09.000')
insert into t_temp values(8,2000,'2006-10-10 11:02:15.000')
insert into t_temp values(9,2000,'2006-10-10 11:02:16.000')
insert into t_temp values(10,2000,'2006-10-10 11:02:24.000')
insert into t_temp values(11,2000,'2006-10-10 11:02:31.000')
insert into t_temp values(12,3000,'2006-10-10 11:00:06.000')
insert into t_temp values(13,3000,'2006-10-10 11:01:06.000')
insert into t_temp values(14,3000,'2006-10-10 11:02:06.000')
insert into t_temp values(15,3000,'2006-10-10 11:03:06.000')
insert into t_temp values(16,3000,'2006-10-10 11:03:26.000')
insert into t_temp values(17,4000,'2006-10-10 10:01:15.000')
insert into t_temp values(18,4000,'2006-10-10 10:01:20.000')
insert into t_temp values(19,4000,'2006-10-10 10:01:21.000')
insert into t_temp values(20,4000,'2006-10-10 10:01:23.000')
insert into t_temp values(21,4000,'2006-10-10 10:01:25.000')
insert into t_temp values(22,4000,'2006-10-10 10:01:26.000')

GO

19 个解决方案

#1

进来学习！

#2

select * from 表 a where a.id=(select id from 表 b where id=a.id and abs(datediff(s,a.stamp,b.stamp))<=5)

#3

select obj_id,count(*) from
(SELECT a.obj_id,a.stamp astamp,b.stamp bstamp
from t_temp a left join t_temp b on a.obj_id=b.obj_id
and b.stamp=(select min(stamp) from t_temp where obj_id=a.obj_id and stamp>a.stamp)
where datediff(s,a.stamp,b.stamp)<=5) m
group by obj_id having count(*)>=3

#4

select obj_id,count(*) from
(SELECT a.obj_id,a.stamp astamp,
(select min(stamp) from t_temp b where b.obj_id=a.obj_id and b.stamp>a.stamp and datediff(s,a.stamp,b.stamp)<=5) bstamp
from t_temp a ) m where bstamp IS not NULL
group by obj_id having count(*)>=3

这样也可以

#5

出现了三次以上的间隔不超过5秒的OBJ_ID
----------------------------------------

要求连续3次吗？

#6

出现了三次以上的间隔不超过5秒的OBJ_ID
----------------------------------------
要求连续3次吗？
----------------------------------------
要求连续的,因为不连续的,有很多的偶然性.

#7

学习

#8

select obj_id from
(
select *,stamp2 = (select max(stamp) from t_temp where obj_id = a.obj_id and id < a.id)
from t_temp a
)tt
where datediff(ss,stamp2,stamp) <= 5
group by obj_id
having count(1) > 3

--结果
1000
4000

#9

哦,必须是三次连续的??

那我那个语句不可以!

#10

select obj_id from
    (select obj_id,astamp,bstamp,(select min(stamp) from t_temp n where n.obj_id=m.obj_id and n.stamp>m.bstamp and datediff(s,m.bstamp,n.stamp)<=5) cstamp
        from
        (
            SELECT a.obj_id,a.stamp astamp,(select min(stamp) from t_temp b where b.obj_id=a.obj_id and b.stamp>a.stamp and datediff(s,a.stamp,b.stamp)<=5) bstamp
                from t_temp a
        ) m
        where bstamp IS not NULL
    ) t where cstamp IS not NULL
group by obj_id having count(*)>=3

一样的道理，连续3次也可以这样写

#11

谢谢各位兄台了
各位兄弟真的不是一般的高

#12

简单一点：

select distinct obj_id from
(
select *,stamp1=(select top 1 stamp from t_temp b where a.obj_id=b.obj_id and datediff(s,a.stamp,b.stamp)<5 order by stamp desc) from t_temp a
)a
group by obj_id,stamp1
having count(*)>=3
order by 1

#13

#14

更正一下我的，有点Bug，来个更简单的：

select * from t_temp a
where (select count(*) from t_temp b where a.obj_id=b.obj_id and a.stamp<=b.stamp and datediff(ss,a.stamp,b.stamp)<=5)>=3
order by 2

#15

#16

select b.obj_id,count(*)
from t_temp b
where datediff(ss,(select a.stamp from t_temp a where a.obj_id=b.obj_id and b.id=a.id+1),b.stamp)<=5
group by b.obj_id
having count(*)>=4

#17

各位真是高手
本来想把它结了
但为了不影响大家的发挥
这个贴子就暂时先不结了

#18

学习~

#19

出现了三次以上的间隔不超过5秒的OBJ_ID
----------------------------------------
要求连续3次吗？
----------------------------------------
要求连续的,因为不连续的,有很多的偶然性.
----------------------------------------

大家注意一下这个~

#20

#1