日期:2014-05-18 浏览次数:20494 次
--求每五行的平均值: -->>>测试数据: go if OBJECT_ID('tbl')is not null drop table tbl go create table tbl( id int identity(1,1), name varchar(1) default 'a', value float ) go insert tbl select null,9 union all select null,19 union all select null,7 union all select null,6 union all select null,10 union all select null,5 union all select null,0 union all select null,6 union all select null,8 union all select null,10 union all select null,2 union all select null,4 union all select null,22 union all select null,12 union all select null,2 select * from tbl go create table #tbla( value float ) go if OBJECT_ID('pro_tracy')is not null drop proc pro_tracy go create proc pro_tracy @maxid int as declare @startid int,@endid int set @startid=1 set @endid=5 --select @maxid=MAX(id) from tbl while @endid<=@maxid begin insert #tbla select AVG(value) from tbl where id between @startid and @endid set @startid=@startid+1 set @endid=@endid+1 end go exec pro_tracy 15--参数15表示的是tbl最大ID go select * from #tbla /* value 10.2 9.4 5.6 5.4 5.8 5.8 5.2 6 9.2 10 8.4 */
------解决方案--------------------
if OBJECT_ID('tbA')is not null Drop table tbA; if OBJECT_ID('tbB')is not null Drop table tbB; go create table tbA(id int identity(1,1),column2 int, value float); create table tbB(id int, avgValue float); go insert tbA(column2, value) select null,9 union all select null,19 union all select null,7 union all select null,6 union all select null,10 union all select null,5 union all select null,0 union all select null,6 union all select null,8 union all select null,10 union all select null,2 union all select null,4 union all select null,22 union all select null,12 union all select null,2; insert into tbB(id, avgValue) select id, avgV = (select avg(value) from tbA b where b.id between a.id and (a.id + 4)) from tbA a where id <= (select MAX(id) from tbA) - 4; select * from tbB; drop table tbA; drop table tbB; /* -- tbB 结果: id avgValue ----------- ---------------------- 1 10.2 2 9.4 3 5.6 4 5.4 5 5.8 6 5.8 7 5.2 8 6 9 9.2 10 10 11 8.4 */
------解决方案--------------------
--求每五行的平均值: --顺带做了一个效率测试 -->>>测试数据: go if OBJECT_ID('tbl')is not null drop table tbl go create table tbl( id int identity(1,1), name varchar(1) default 'a', value float ) go declare @a int set @a=1 while @a<=100000 begin insert tbl select null,9 union all select null,19 union all select null,7 union all select null,6 union all select null,10 union all select null,5 union all select null,0 union all select null,6 union all select null,8 union all select null,10 union all select null,2 union all select null,4 union all select null,22 union all select null,12 union all select null,2 set @a=@a+1 end select * from tbl go create table #tbl( value float ) --方法1 insert into #tbl select (select avg(value) from tbl b where b.id between a.id and (a.id + 4)) from tbl a where id <= (select MAX(id)-4 from tbl) --执行时间28s --方法2(效率很差) go if OBJECT_ID('pro_tracy')is not null drop proc pro_tracy go create proc pro_tracy as declare @startid int,@endid int set @startid=1 set @endid=5 while @endid<=(select MAX(id) from tbl) begin insert #tbl select AVG(value) from tbl where id between @startid and @endid set @startid=@startid+1 set @endid=@endid+1 end go exec pro_tracy --运行63s,结果79行数据 go select * from #tbl
------解决方案--------------------