--按日 select sum(consume),day([date]) from consume_record where year([date]) = ‘2006‘ group by day([date])
--按周quarter select sum(consume),datename(week,[date]) from consume_record where year([date]) = ‘2006‘ group by datename(week,[date])
--按月 select sum(consume),month([date]) from consume_record where year([date]) = ‘2006‘ group by month([date])
--按季 select sum(consume),datename(quarter,[date]) from consume_record where year([date]) = ‘2006‘ group by datename(quarter,[date])
--按年 select sum(consume),year([date]) from consume_record where group by year([date])
--这个按每五分钟的[code=SQL]create table tb(时间 datetime , 金额 int)
insert into tb values(‘2007-1-1 10:00:23‘ , 8 )
insert into tb values(‘2007-1-1 10:01:24‘ , 4 )
insert into tb values(‘2007-1-1 10:05:00‘ , 2 )
insert into tb values(‘2007-1-1 10:06:12‘ , 3 )
insert into tb values(‘2007-1-1 10:08:00‘ , 1 )
insert into tb values(‘2007-1-1 10:12:11‘ , 5 )
go--时间段>=10:00:00 and 时间段<10:05:00select dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,-1,时间),120),dateadd(ss,-1,时间))/5)*5,convert(varchar(10),时间,120)) as 时间段,
count(*) as 行数,
sum(金额) as 总金额
from tb
group by dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,-1,时间),120),dateadd(ss,-1,时间))/5)*5,convert(varchar(10),时间,120))
/*时间段 行数 总金额 ------------------------------------------------------ ----------- ----------- 2007-01-01 10:00:00.000 3 142007-01-01 10:05:00.000 2 42007-01-01 10:10:00.000 1 5（所影响的行数为 3 行）*/--时间段>10:00:00 and 时间段<=10:05:00select dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,1,时间),120),dateadd(ss,1,时间))/5)*5,convert(varchar(10),时间,120)) as 时间段,
count(*) as 行数,
sum(金额) as 总金额
from tb
group by dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,1,时间),120),dateadd(ss,1,时间))/5)*5,convert(varchar(10),时间,120))
/*时间段 行数 总金额 ------------------------------------------------------ ----------- ----------- 2007-01-01 10:00:00.000 2 122007-01-01 10:05:00.000 3 62007-01-01 10:10:00.000 1 5（所影响的行数为 3 行）*/drop table tb