SQL statistics by day, week, month, year, time period

--按日 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

SQL statistics by day, week, month, year, time period