select * from 表 a where a.id=(select id from 表 b where id=a.id and abs(datediff(s,a.stamp,b.stamp))<=5)
解决方案 »
- 关于一个行列转换的问题
- [重发 再加50分 共150 ]---异表批量更新字段值-----难题
- 现在在数据列表中插入UTF-8编码数据,在数据库中的 varchar类型是否要修改?
- 比较复杂的sql语句,请高手帮忙?
- sql server数据库连接的方法有几种(asp.net中)
- SQL结果集没有ID求分页显示最好是高效一点的
- 我不会的sql,求帮助······
- 帮我看一下这个出入库存汇总怎么写?谢谢.
- 日期类型量的添加
- 数据库创建问题,解答出来一定给分
- 请教各位如何更好优化sql server的后台,以提高存储过程的速度
- SqlServer2000和SqlServer2005附加数据库602错误
(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
(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这样也可以
----------------------------------------要求连续3次吗?
----------------------------------------
要求连续3次吗?
----------------------------------------
要求连续的,因为不连续的,有很多的偶然性.
(
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
(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次也可以这样写
(
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
(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
这么些应该对阿
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
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
出现了三次以上的间隔不超过5秒的OBJ_ID
----------------------------------------
要求连续3次吗?
----------------------------------------
要求连续的,因为不连续的,有很多的偶然性.
----------------------------------------大家注意一下这个~