查询！如何按时间分组？

[常飞梦]

有一个表，有一个时间字段，我想统计一下每间隔5分钟的记录数,我如何按时间分组呢？

1 2006-03-01 08:41:32.387
2 2006-03-01 08:41:32.560
3 2006-03-01 08:41:32.623
4 2006-03-01 08:41:32.887
5 2006-03-01 08:41:33.327
6 2006-03-01 13:04:39.107
7 2006-03-01 16:46:52.810
8 2006-03-01 16:46:53.233
9 2006-03-01 16:46:53.280
10 2006-03-01 16:46:53.420
11 2006-03-01 16:46:53.623
12 2006-03-01 18:23:00.123
13 2006-03-01 18:23:00.263
14 2006-03-01 18:23:00.513
15 2006-03-01 18:23:00.700
16 2006-03-01 18:23:00.937
17 2006-03-01 20:02:11.437
18 2006-03-01 20:02:11.483
19 2006-03-01 20:02:11.763
20 2006-03-01 20:02:11.967

[huailairen]

做一个视图，新加一列作为group 列
create view tmp
as
select 列1,时间列, substring(cast(时间列 as varchar(20)),1,14)+
cast(datepart(mm,时间列)/5 as varchar(2)) as glie
from 表

使用group by
select glie
from tmp
group by glie

[aniude]

----------
select 时间字段,记录数=count(时间字段) from tb group by 时间字段
不太知道怎么按照5分钟算记录数

[sutdy]

同意huailairen(流浪猫)，不过有个地方需要改改：cast(时间列 as varchar(20))改为convert函数convert（varchar(20),时间列，20）好些。

[nowordseagle]

看 是否能使用 group by function()/5
function () 可以在查询分析器里查出来 只要取其中的分钟数 参数大概是 mm zhilei

[十一月猪]

declare @t table(id int , time1 datetime)
declare @t1 table(count1 int,begin_time datetime, end_time datetime)
insert into @t
select 1 ,'2006-03-01 08:41:32.387' union all
select 2, '2006-03-01 08:41:32.560' union all
select 3, '2006-03-01 08:41:32.623' union all
select 4, '2006-03-01 08:41:32.887' union all
select 5, '2006-03-01 08:41:33.327' union all
select 6, '2006-03-01 13:04:39.107' union all
select 7, '2006-03-01 16:46:52.810' union all
select 8, '2006-03-01 16:46:53.233' union all
select 9, '2006-03-01 16:46:53.280' union all
select 10, '2006-03-01 16:46:53.420' union all
select 11, '2006-03-01 16:46:53.623' union all
select 12, '2006-03-01 18:23:00.123' union all
select 13, '2006-03-01 18:23:00.263' union all
select 14, '2006-03-01 18:23:00.513' union all
select 15, '2006-03-01 18:23:00.700' union all
select 16, '2006-03-01 18:23:00.937' union all
select 17, '2006-03-01 20:02:11.437' union all
select 18, '2006-03-01 20:02:11.483' union all
select 19, '2006-03-01 20:02:11.763' union all
select 20, '2006-03-01 20:02:11.967'

declare @sum_count int
declare @sum_count1 int
declare @sum_count2 int
declare @begin_time datetime
declare @end_time datetime

select @begin_time = time1 from @t
where id = 1

select @sum_count1 = 0
select @sum_count2 = 0
select @sum_count = count(*) from @t

while (@sum_count > @sum_count1 )

begin
select @end_time = dateadd(mi,5,@begin_time)
select @sum_count2 = count(*) from @t
where time1 between @begin_time and @end_time

insert into @t1
select @sum_count2 ,@begin_time ,@end_time

select @sum_count1 = @sum_count2 + @sum_count1

select @begin_time = time1 from @t
where id = @sum_count1 + 1
end

select * from @t1