time name
2010-12-11 08:20:02 a
2010-12-11 08:20:04 a
2010-12-11 08:20:15 a
2010-12-11 08:20:35 a
2010-12-11 08:20:50 b
基本条件:时间>3秒 ,<30分钟。希望得到结果name time count startTime endTime
a 2010-12-11 08:20:04 3 2010-12-11 08:20:04 2010-12-11 08:20:35
b 2010-12-11 08:20:50 1 2010-12-11 08:20:50 2010-12-11 08:20:50
2010-12-11 08:20:02 a
2010-12-11 08:20:04 a
2010-12-11 08:20:15 a
2010-12-11 08:20:35 a
2010-12-11 08:20:50 b
基本条件:时间>3秒 ,<30分钟。希望得到结果name time count startTime endTime
a 2010-12-11 08:20:04 3 2010-12-11 08:20:04 2010-12-11 08:20:35
b 2010-12-11 08:20:50 1 2010-12-11 08:20:50 2010-12-11 08:20:50
t n
select '2010-12-11 08:20:02', 'a' union all
select '2010-12-11 08:20:04', 'a' union all
select '2010-12-11 08:20:15', 'a' union all
select '2010-12-11 08:20:35', 'a' union all
select '2010-12-11 08:20:50', 'b'*/
select name=min(n),time=min(t),[count]=count(*),startTime=min(t),endTime=max(t) from tb where datepart(mm,t)<30 and datepart(ss,t)>3 group by n/*
name time count startTime endTime
a 2010-12-11 08:20:04 3 2010-12-11 08:20:04 2010-12-11 08:20:35
b 2010-12-11 08:20:50 1 2010-12-11 08:20:50 2010-12-11 08:20:50
*/
(time datetime , name varchar(1))
insert into @t(time,name)
values
('2010-12-11 08:20:02','a'),
('2010-12-11 08:20:04','a'),
('2010-12-11 08:20:15','a'),
('2010-12-11 08:20:35','a'),
('2010-12-11 08:20:50','b')select name,MIN(time) as time,COUNT(time),MIN(time) as startTime,max(time) as endTime
from @t where datepart(ss,time)>3 and datepart(N,TIME)<30
group by name -----------------------------------
a 2010-12-11 08:20:04.000 3 2010-12-11 08:20:04.000 2010-12-11 08:20:35.000
b 2010-12-11 08:20:50.000 1 2010-12-11 08:20:50.000 2010-12-11 08:20:50.000
from tb
where datepart(mm,time)<30 and datepart(ss,time)>3 group by [name]
from tb
where datepart(mi,time)<30 and datepart(ss,time)>3 group by [name]