SQL按照日、周、月、年、时间段统计数据

--按日 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:00 select 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           14 2007-01-01 10:05:00.000                                2           4 2007-01-01 10:10:00.000                                1           5 （所影响的行数为 3 行） */   --时间段>10:00:00 and 时间段<=10:05:00 select 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           12 2007-01-01 10:05:00.000                                3           6 2007-01-01 10:10:00.000                                1           5   （所影响的行数为 3 行） */   drop table tb