--求每五行的平均值：
-->>>测试数据:
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
*/

------解决方案--------------------

SQL code

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
*/

------解决方案--------------------

SQL code

--求每五行的平均值：
--顺带做了一个效率测试
-->>>测试数据:
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

------解决方案--------------------

SQL code