34,590
社区成员
发帖
与我相关
我的任务
分享
create table tb(time datetime,name varchar(10))
insert tb select '2010-12-11 08:20:02', 'a'
insert tb select '2010-12-11 08:20:04', 'a'
insert tb select '2010-12-11 08:20:15', 'a'
insert tb select '2010-12-11 08:20:55', 'a'
insert tb select '2010-12-11 08:20:50', 'b'
go
with t1 as
(
select rowid = row_number() over(partition by name order by time),* from tb
)
,t2 as
(
select a.*,endTime = isnull(b.time,a.time)
from t1 a
left join t1 b
on a.name = b.name and b.rowid = a.rowid + 1 and b.time > dateadd(s,3,a.time) and b.time < dateadd(s,30,a.time)
)
select name,count = count(1),startTime = min(time),endTime
from t2
group by name,endTime
order by name,endTime
/*
name count startTime endTime
---------- ----------- ----------------------- -----------------------
a 1 2010-12-11 08:20:02.000 2010-12-11 08:20:02.000
a 2 2010-12-11 08:20:04.000 2010-12-11 08:20:15.000
a 1 2010-12-11 08:20:55.000 2010-12-11 08:20:55.000
b 1 2010-12-11 08:20:50.000 2010-12-11 08:20:50.000
(4 行受影响)
*/
没看明白