SQL 按 时间段 进行分组统计

[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 tbgroup 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 tbgroup 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

转载http://bbs.csdn.net/topics/360003771