生气了按照时间段分组查询统计——shalfen

--dbs 的 表结构 是 id(int),num(int),date(datetime) drop procedure dbo.fenzu create procedure dbo.fenzu @begin_time datetime, @end_time datetime, @type varchar(10), @many int --这些是传入的参数前边两个是岂止时间 --type是分段单位 :年是'year','yy'或者'yyyy';季度是'quarter','qq','q';月是 'month','mm','m';日是'day','dd','d';时是 'hour','hh';分是 'minute','mi','n';秒是'second','ss','s';毫秒是'millisecond','ms') --many 是 长度，即 几年分一组，几天分一组，几秒分一组！ as select top 1 @begin_time = date from dbs where date >= @begin_time --给岂止时间赋值(精确时间) select top 1 @end_time = date from dbs where date <= @end_time order by date desc declare @t table(id int,num int,begin_time datetime,end_time datetime)--创建一个临时表--用来存放数据 declare @num int,@count int,@count1 int--声明几个变量:num是要统计的数字(可以变,我这里是查询每个时间段的某个数据的平均值),count是在整个时间范围内,要查询的表的总记录数,count1是用来存放下边循环的每组的统计记录数就是说,每组统计了几条数据 declare @i int,@id int--i是从1开始,用于循环 select @num = 0--给num赋初值 select @count = count(*) from dbs where date >= @begin_time and date <= @end_time--赋值总记录数 select @i = 1--从1开始,用于循环 select @id = 1--便于往临时表里插入id while(@i<=@count) begin select @end_time= ( case @type when '年' then dateadd(year,@many,@begin_time) when '季' then dateadd(quarter,@many,@begin_time) when '月' then dateadd(month,@many,@begin_time) when '周' then dateadd(week,@many,@begin_time) when '日' then dateadd(day,@many,@begin_time) when '时' then dateadd(hour,@many,@begin_time) when '分' then dateadd(minute,@many,@begin_time) when '秒' then dateadd(second,@many,@begin_time) when '毫秒' then dateadd(millisecond,@many,@begin_time) end ) select @num = avg(num) from dbs where date >= @begin_time and date < @end_time select @count1 = count(*) from dbs where date >= @begin_time and date < @end_time if(@count1 = 0) begin select @num = 0 end insert into @t select @id,@num,@begin_time,@end_time select @id = @id +1 select @i= @i+@count1 --select top 1 @begin_time = date from dbs where date <= @end_time order by date desc----这个是按照表里的记录走的 select @begin_time = @end_time---这个是按照时刻走的 end select * from @t go exec fenzu @begin_time = '2010-01-02 00:00:00.000',@end_time = '2010-05-01 00:00:00.000',@type='分',@many=30