这个意思吗
declare @t table(username varchar(10),logtime datetime)
insert @t select 'a','2001-01-01 10:01:00'
insert @t select 'a','2001-01-01 10:11:00'
insert @t select 'a','2001-01-01 10:12:00'
insert @t select 'a','2001-01-01 10:32:00'
insert @t select 'a','2001-01-01 10:33:00'
insert @t select 'a','2001-01-01 10:34:00'
insert @t select 'a','2001-01-01 10:52:00'
select a.*
from (select *,px = (select count(1) from @t where pa.username = username and logtime < pa.logtime) from @t pa) a
left join
(select *,px = (select count(1) from @t where pa.username = username and logtime < pa.logtime) from @t pa) b
on datediff(mi,a.logtime,b.logtime) > 5 and a.px = b.px - 1
where b.username is not null/*
username logtime px
---------- ------------------------------------------------------ -----------
a 2001-01-01 10:01:00.000 0
a 2001-01-01 10:12:00.000 2
a 2001-01-01 10:34:00.000 5(所影响的行数为 3 行)
*/
declare @t table(username varchar(10),logtime datetime)
insert @t select 'a','2001-01-01 10:01:00'
insert @t select 'a','2001-01-01 10:11:00'
insert @t select 'a','2001-01-01 10:12:00'
insert @t select 'a','2001-01-01 10:32:00'
insert @t select 'a','2001-01-01 10:33:00'
insert @t select 'a','2001-01-01 10:34:00'
insert @t select 'a','2001-01-01 10:52:00'
select a.*
from (select *,px = (select count(1) from @t where pa.username = username and logtime < pa.logtime) from @t pa) a
left join
(select *,px = (select count(1) from @t where pa.username = username and logtime < pa.logtime) from @t pa) b
on datediff(mi,a.logtime,b.logtime) > 5 and a.px = b.px - 1
where b.username is not null/*
username logtime px
---------- ------------------------------------------------------ -----------
a 2001-01-01 10:01:00.000 0
a 2001-01-01 10:12:00.000 2
a 2001-01-01 10:34:00.000 5(所影响的行数为 3 行)
*/
select
a.*
from
T a
join
(select distinct 用户名
from T a
where
datediff(n,登陆时间,(select max(登陆时间) from t where 用户名=a.用户名 and 登陆时间>a.登陆时间))>=5--大于等于5分钟
)b on a.用户名=b.用户名
select
a.*
from
T a
join
(select distinct 用户名
from T a
where
datediff(n,登陆时间,(select min(登陆时间) from t where 用户名=a.用户名 and 登陆时间>a.登陆时间))>=5--大于等于5分钟
)b on a.用户名=b.用户名
a 2001-01-01 10:12:00.000 2
a 2001-01-01 10:34:00.000 5
这个结果好像不对啊?