表内容如下
-----------------------------
ID LogTime
1 2008/10/10 10:00:00
1 2008/10/10 10:03:00
1 2008/10/10 10:09:00
2 2008/10/10 10:10:00
2 2008/10/10 10:11:00
......
-----------------------------请问各位高手,如何查询登陆时间间隔不超过5分钟的所有记录. 万分感谢.感谢感谢感谢感谢感谢...
-----------------------------
ID LogTime
1 2008/10/10 10:00:00
1 2008/10/10 10:03:00
1 2008/10/10 10:09:00
2 2008/10/10 10:10:00
2 2008/10/10 10:11:00
......
-----------------------------请问各位高手,如何查询登陆时间间隔不超过5分钟的所有记录. 万分感谢.感谢感谢感谢感谢感谢...
解决方案 »
- 数据库表操作的问题:三列合成一列????
- sql 连接两个字符变量的问题
- ID与id=id*1有什么区别
- 当用用户回头率分析,一天算一次,如果一天来访了N次也算一次。第二天再来才能加一次
- sql server 2000 的数据文件损坏无法附加怎么办啊
- full join 怎么不行啊???
- 写了一个存储过程,执行后有两个RECORDSET,如何返回指定的RECORDSET?
- 作业调度突然不执行了!
- 如何批量把图片存入数据中对应的字段?
- 高分寻求在数据库开发的实际项目中用到的有关技术!
- 关于删除有外键约束的记录的存储过程
- 通过第三张表的信息找到第二张表的一个关键字,再找到第一张表的信息,请问这样的SQL语句写法是不是最合适?还有没有更好的写法?
select m.id , m.logtime from
(select t.* , px = (select count(*) from tb where logtime < t.logtime) + 1 from tb t) m,
(select t.* , px = (select count(*) from tb where logtime < t.logtime) + 1 from tb t) n
where m.px = n.px - 1 and datediff(mi , m.logtime,n.logtime) <= 5--分ID计算
select m.id , m.logtime from
(select t.* , px = (select count(*) from tb where id = t.id and logtime < t.logtime) + 1 from tb t) m,
(select t.* , px = (select count(*) from tb where id = t.id and logtime < t.logtime) + 1 from tb t) n
where m.id = n.id and m.px = n.px - 1 and datediff(mi , m.logtime,n.logtime) <= 5
from tb a
where not exists(select 1 from tb where logtime>a.logtime and datediff(minute,a.logtime,logtime)<5)
declare @T table (ID int,LogTime datetime)
insert into @T
select 1,'2008/10/10 10:00:00' union all
select 1,'2008/10/10 10:03:00' union all
select 1,'2008/10/10 10:09:00' union all
select 2,'2008/10/10 10:10:00' union all
select 2,'2008/10/10 10:11:00'-->取小的
select * from @T a where exists (select 1 from @T b where ID = a.ID and LogTime>a.LogTime and datediff(minute,a.LogTime,LogTime)<5)
/*
1 2008-10-10 10:00:00.000
2 2008-10-10 10:10:00.000
*/-->取大的
select * from @T a where exists (select 1 from @T b where ID = a.ID and LogTime<a.LogTime and datediff(minute,LogTime,a.LogTime)<5)
/*
1 2008-10-10 10:03:00.000
2 2008-10-10 10:11:00.000
*/
---- 按全部记录id logtime
----------- -----------------------
1 2008-10-10 10:00:00.000
1 2008-10-10 10:09:00.000
2 2008-10-10 10:10:00.000
2 2008-10-10 10:11:00.000
1 2008-10-11 09:15:00.000
2 2008-10-11 09:19:00.000(6 行受影响)---- 分ID计算
id logtime
----------- -----------------------
1 2008-10-10 10:00:00.000
2 2008-10-10 10:10:00.000
1 2008-10-10 10:09:00.000
2 2008-10-11 09:19:00.000(4 行受影响)
where datediff(n,a.logtime,b.logtime)<5 and a.logtime<b.logtime and a.id=b.id
第一次回帖哦