怎么统计在一段连续时间的记录数量

[Thanks4Help]

比如:
ID 时间 记录号码
1 2005-12-26 12:00:00 1111
2 2005-12-26 12:00:01 2222
3 2005-12-26 12:00:02 1111
4 2005-12-26 12:10:03 2222
5 2005-12-26 12:10:04 1111
6 2005-12-26 12:10:05 1111
7 2005-12-26 12:20:06 2222
8 2005-12-26 12:20:07 1111
9 2005-12-26 12:20:08 2222
10 2005-12-26 12:20:09 1111

统计最后一批连续记录号为1111的记录数量,
假设间隔小于1分钟的为连续记录,那么查询结果就是

8 2005-12-26 12:20:07 1111
10 2005-12-26 12:20:09 1111

结果为2个记录
因为记录6的时间与8的时间间隔大于1分钟

不知道大家明白我的意思.

我希望创建一个过程,参数为 连续间隔的时间 和 记录号
结果就是统计最后一批的连续数量或者记录

谢谢!!!

[cosio]

用游标一条一条是可以，只是效率不高！

[cosio]

create procedure sp_temp
(

@time datetime, ---相隔的秒数
@ID int

)


Declare @Time1 datetime
Declare @Time2 datetime
declare @number char(20)
declare @time3 datetime

select @Time1= Time , @mnumber =mnumber from @t where id=@id

begin

declare Tempcur cursor
for
select @time2= time from @t where id<@id

open tempcur
fetch next from tempcur into @time

while @@fetch_status=0
begin

select * from @T where datediff(second,@time3,@time1) <@time and mnumber =@number


end
end



没有写完，因过程要去调试比较麻烦，所以就不写了

[mislrb]

create table t (id int,rec_time datetime,rec_no varchar(4))
insert t
select 1, '2005-12-26 12:00:00', '1111' union all
select 2, '2005-12-26 12:00:01', '2222' union all
select 3, '2005-12-26 12:00:02', '1111' union all
select 4, '2005-12-26 12:10:03', '2222' union all
select 5, '2005-12-26 12:10:04', '1111' union all
select 6, '2005-12-26 12:10:05', '1111' union all
select 7, '2005-12-26 12:20:06', '2222' union all
select 8, '2005-12-26 12:20:07', '1111' union all
select 9, '2005-12-26 12:20:08', '2222' union all
select 10, '2005-12-26 12:20:09', '1111'

if exists(select 1 from sysobjects where id=object_id(N'p_getResult') and xtype='P')
drop procedure p_getResult
go
create procedure p_getResult
@stDate datetime ='2005-12-26 12:00:00',
@enDate datetime ='2005-12-26 12:20:09',
@interval int =1, --minute
@id int ='1111'
as
select nid=identity(int,1,1),*
into #t
from t
where rec_no=@id and
rec_time between @stDate and @enDate
order by rec_time desc

select distinct top 2 a.id,a.rec_time,a.rec_no
from #t a,#t b
where exists(select 1 from #t where nid=a.nid+1 and datediff(second,b.rec_time,a.rec_time)<@interval)
order by a.rec_time desc
drop table #t
go

exec dbo.p_getResult '2005-12-26 12:00:00','2005-12-26 12:20:09',60,'1111'

exec dbo.p_getResult

/*
id rec_time rec_no
----------- ------------------------------------------------------ ------
10 2005-12-26 12:20:09.000 1111
8 2005-12-26 12:20:07.000 1111


*/

[Thanks4Help]

to:cosio()
谢谢!能否不使用游标啊!游标是慢了点

to:mislrb(aben)
谢谢!我不想输入一个时间段.最好能够自己找到最后一批,没有时间段的限制.

[liujx_1999]

create table t (id int,_datetime datetime,_count varchar(4))
insert t
select 1, '2005-12-26 12:00:00', '1111' union all
select 2, '2005-12-26 12:00:01', '2222' union all
select 3, '2005-12-26 12:00:02', '1111' union all
select 4, '2005-12-26 12:10:03', '2222' union all
select 5, '2005-12-26 12:10:04', '1111' union all
select 6, '2005-12-26 12:10:05', '1111' union all
select 7, '2005-12-26 12:20:06', '2222' union all
select 8, '2005-12-26 12:20:07', '1111' union all
select 9, '2005-12-26 12:20:08', '2222' union all
select 10, '2005-12-26 12:20:09', '1111'

create proc sp_returncount
@datetime_B datetime,
@datetime_E datetime,
@count int
as

select Convert(varchar(16),_datetime,120) as _datetime , _count into #tt from t where _datetime between @datetime_B and @datetime_E
group by Convert(varchar(16),_datetime,120),_count having count(*)>1
select * from t A where EXISTS (select * FROM #tt B WHERE Convert(varchar(16),a._datetime,120)=Convert(varchar(16),b._datetime,120) AND a._count=b._count) and a._count=@count

drop table #tt
Go
exec sp_returncount '2005-12-26 12:00:00','2005-12-26 12:20:00',1111
/*
id _datetime _count
----------- ---------------------- ------
1 2005-12-26 12:00:00.000 1111
3 2005-12-26 12:00:02.000 1111
5 2005-12-26 12:10:04.000 1111
6 2005-12-26 12:10:05.000 1111
*/

[mislrb]

我不想输入一个时间段.最好能够自己找到最后一批,没有时间段的限制.

---------------------------------------------------------------
自己不能改改用么，也太懒了兄弟

不要时间段限制，去掉就行了啊，加个时段限制也是为了更好的性能，如果记录到时疯长几百万条，不知道select into的执行时间你还能不能接受

[liujx_1999]

create table t (id int,_datetime datetime,_count varchar(4))
insert t
select 1, '2005-12-26 12:00:00', '1111' union all
select 2, '2005-12-26 12:00:01', '2222' union all
select 3, '2005-12-26 12:00:02', '1111' union all
select 4, '2005-12-26 12:10:03', '2222' union all
select 5, '2005-12-26 12:10:04', '1111' union all
select 6, '2005-12-26 12:10:05', '1111' union all
select 7, '2005-12-26 12:20:06', '2222' union all
select 8, '2005-12-26 12:20:07', '1111' union all
select 9, '2005-12-26 12:20:08', '2222' union all
select 10, '2005-12-26 12:20:09', '1111'

alter proc sp_returncount
@count int
as

select Convert(varchar(16),_datetime,120) as _datetime , _count into #tt from t --where _datetime between @datetime_B and @datetime_E
group by Convert(varchar(16),_datetime,120),_count having count(*)>1


select top 1 * into #BB from #tt where _count=@count order by _datetime desc

select * from t A where EXISTS (select * FROM #BB B WHERE Convert(varchar(16),a._datetime,120)=Convert(varchar(16),b._datetime,120) AND a._count=b._count) and a._count=@count
drop table #tt,#BB
Go
exec sp_returncount 1111
/*
id _datetime _count
----------- ---------------------- ------
8 2005-12-26 12:20:07.000 1111
10 2005-12-26 12:20:09.000 1111
*/

[yingfeiqiyue]

看了半天还没有明白！
该学习了