写个函数,判断他是哪一个范围的再汇总~~~create table test(rt datetime,a int,b int)
insert test select '2006-01-01 00:00:00',3,4
insert test select '2006-01-01 00:00:00',3,4
insert test select '2006-01-01 00:01:00',3,4
insert test select '2006-01-01 00:03:00',3,4
insert test select '2006-01-01 00:04:00',3,4
insert test select '2006-01-01 00:06:00',3,4
select * from test
gocreate function ftest(@dt1 datetime,@dt2 datetime,@cz int)
returns int
as
begin
return datediff(minute,@dt1,@dt2)/@cz
end
goselect
dbo.ftest('2006-01-01 00:00:00',rt,2) as 区间,
sum(a) as a,
sum(b) as b
from test
group by dbo.ftest('2006-01-01 00:00:00',rt,2)drop function ftest
drop table test
insert test select '2006-01-01 00:00:00',3,4
insert test select '2006-01-01 00:00:00',3,4
insert test select '2006-01-01 00:01:00',3,4
insert test select '2006-01-01 00:03:00',3,4
insert test select '2006-01-01 00:04:00',3,4
insert test select '2006-01-01 00:06:00',3,4
select * from test
gocreate function ftest(@dt1 datetime,@dt2 datetime,@cz int)
returns int
as
begin
return datediff(minute,@dt1,@dt2)/@cz
end
goselect
dbo.ftest('2006-01-01 00:00:00',rt,2) as 区间,
sum(a) as a,
sum(b) as b
from test
group by dbo.ftest('2006-01-01 00:00:00',rt,2)drop function ftest
drop table test
select cast('2006-01-01 00:00:00' as datetime) as rt, 1 as a, 1 as b
into test
union select '2006-01-01 00:01:00', 2, 1
union select '2006-01-01 00:02:00', 3, 1
union select '2006-01-01 00:03:00', 4, 1
union select '2006-01-01 00:04:00', 5, 1
union select '2006-01-01 00:05:00', 6, 1
union select '2006-01-01 00:06:00', 7, 1
union select '2006-01-01 00:07:00', 8, 1
union select '2006-01-01 00:08:00', 9, 1
--------------------------------------------------
declare @n int
set @n = 5 --5分钟
select dateadd(minute, rt * @n, '2006-01-01 00:00:00') as rt, a
from (select datediff(minute, '2006-01-01 00:00:00', rt) / @n as rt, max(a) as a
from test
group by datediff(minute, '2006-01-01 00:00:00', rt) / @n) t1
/*
rt a
2006-01-01 00:00:00.000 5
2006-01-01 00:05:00.000 9*/
--或者直接这样
select dateadd(minute, datediff(minute, '2006-01-01 00:00:00', rt) / @n * @n, '2006-01-01 00:00:00') as rt, max(a) as a
from test
group by dateadd(minute, datediff(minute, '2006-01-01 00:00:00', rt) / @n * @n, '2006-01-01 00:00:00')
/*
rt a
2006-01-01 00:00:00.000 5
2006-01-01 00:05:00.000 9*/
--------------------------------------------------
drop table test