我的一张表里面有一事件类型字断:
我想给定一个时间,查询从这个时间开始,每10分钟之内的记录数:select count(1) from table
where firstreceiveddate>='2007-01-31 15:29:17'
and firstreceiveddate<=dateadd(minute,10,'2007-1-31 15:29:17') 只能计算一个时间段,因为时间间隔可能变化,如何动态计算?
需要得到结果:
1 10
2 30
3 40
4 5
......一直计算从给定时间到最近时间所有时间段,请教!
难道真的要用游标?
我想给定一个时间,查询从这个时间开始,每10分钟之内的记录数:select count(1) from table
where firstreceiveddate>='2007-01-31 15:29:17'
and firstreceiveddate<=dateadd(minute,10,'2007-1-31 15:29:17') 只能计算一个时间段,因为时间间隔可能变化,如何动态计算?
需要得到结果:
1 10
2 30
3 40
4 5
......一直计算从给定时间到最近时间所有时间段,请教!
难道真的要用游标?
set @i=0
declare @max_firstreceiveddate datetime,@start_time datetime
set @start_time = '2007-1-31 15:29:17'
select @max_firstreceiveddate = max(firstreceiveddate) from csdn
set @n = datediff(mi,@start_time,@max_firstreceiveddate)/10
declare @tvalue table
(
num int
)while(@i<=@n)
begin
insert into @tvalue select count(*) as num from (select firstreceiveddate from csdn
where firstreceiveddate>=@start_time
group by firstreceiveddate
having datediff(mi,@start_time,firstreceiveddate)>=@i*10 and datediff(mi,@start_time,firstreceiveddate)<(@i+1)*10) a
set @i = @i + 1
endselect * from @tvalue