数据整编的问题，急于请教

[colafei]

在SQL SERVER数据库中，我原来的数据是1分钟三条数据，我现在要整编成5分钟一条数据，数据值是这5分钟中数据的平均值。
code tm value
针对某个code进行计算，难道每次都要计算分钟上0--5---10－－15...这样一个一个的 计算吗？

[playwarcraft]

--try
--tm為datetime
select avg(value)
from T
group by code,convert(char(13),tm,120),
convert(int,substring(convert(varchar(20),getdate(),120),15,2))/5

[colafei]

呵呵，大哥，我看了半天还不是很明白你的意思
能够详细点吗？

[playwarcraft]

--給你看下結果
create table t (code int,tm datetime,value int)
insert into t
select 1,'2006-09-16 00:00:10',10 union all
select 1,'2006-09-16 00:00:30',20 union all
select 1,'2006-09-16 00:00:50',30 union all

select 1,'2006-09-16 00:01:10',10 union all
select 1,'2006-09-16 00:01:30',20 union all
select 1,'2006-09-16 00:01:50',30 union all

select 1,'2006-09-16 00:02:10',10 union all
select 1,'2006-09-16 00:02:30',20 union all
select 1,'2006-09-16 00:02:50',30 union all

select 1,'2006-09-16 00:03:10',10 union all
select 1,'2006-09-16 00:03:30',20 union all
select 1,'2006-09-16 00:03:50',30 union all

select 1,'2006-09-16 00:04:10',10 union all
select 1,'2006-09-16 00:04:30',20 union all
select 1,'2006-09-16 00:04:50',30 union all

select 1,'2006-09-16 00:05:10',1 union all
select 1,'2006-09-16 00:05:30',2 union all
select 1,'2006-09-16 00:05:50',3 union all

select 1,'2006-09-16 00:06:10',1 union all
select 1,'2006-09-16 00:06:30',2 union all
select 1,'2006-09-16 00:06:50',3 union all

select 1,'2006-09-16 00:07:10',1 union all
select 1,'2006-09-16 00:07:30',2 union all
select 1,'2006-09-16 00:07:50',3 union all

select 1,'2006-09-16 00:08:10',1 union all
select 1,'2006-09-16 00:08:30',2 union all
select 1,'2006-09-16 00:08:50',3 union all

select 1,'2006-09-16 00:09:10',1 union all
select 1,'2006-09-16 00:09:30',2 union all
select 1,'2006-09-16 00:09:50',3
union all
select 2,'2006-09-16 01:09:50',100

-------------------------------------------------
declare @tm datetime
set @tm=(select min(tm) from t)

select * into #t from
(select code,datediff(minute ,@tm,tm)/5 as min_time, avg(value) as value
from T
group by code,datediff(minute ,@tm,tm)/5) A

select code,convert(varchar(16),dateadd(minute,min_time*5,@tm),120)+'到'+convert(varchar(16),dateadd(minute,(min_time+1)*5-1,@tm),120) as time_range,value from #t


drop table t
drop table #t


/**結果:**/
code time_range value
----------- ---------------------------------- -----------
1 2006-09-16 00:00到2006-09-16 00:04 20
1 2006-09-16 00:05到2006-09-16 00:09 2
2 2006-09-16 01:05到2006-09-16 01:09 100