求助~SQL编程~
自己菜鸟，请高手帮忙解答~
现有两个表A,B
将A表的第3列中的数据，进行每五行的平均值运算
得到的数据写入表B
例如：A表第三列的1~5行求平均，平均值结果写入B表第一行，而后将A表第三列的2~6行求平均，结果写入B表第二行，以此类推~
实现的时候用什么无所谓，但是要在SQL Server中写~
不知哪位大虾能帮我看看？
[解决办法]
--求每五行的平均值：-->>>测试数据:goif OBJECT_ID('tbl')is not nulldrop table tblgocreate table tbl(id int identity(1,1),name varchar(1) default 'a',value float)goinsert tblselect null,9 union allselect null,19 union allselect null,7 union allselect null,6 union allselect null,10 union allselect null,5 union allselect null,0 union allselect null,6 union allselect null,8 union allselect null,10 union allselect null,2 union allselect null,4 union allselect null,22 union allselect null,12 union allselect null,2select * from tblgocreate table #tbla(value float)goif OBJECT_ID('pro_tracy')is not nulldrop proc pro_tracygocreate proc pro_tracy @maxid intasdeclare @startid int,@endid intset @startid=1set @endid=5--select @maxid=MAX(id) from tblwhile @endid<=@maxidbegininsert #tblaselect AVG(value) from tbl where id between @startid and @endidset @startid=@startid+1set @endid=@endid+1endgoexec pro_tracy 15--参数15表示的是tbl最大IDgoselect * from #tbla/*value10.29.45.65.45.85.85.269.2108.4*/
[解决办法]
if OBJECT_ID('tbA')is not null Drop table tbA;if OBJECT_ID('tbB')is not null Drop table tbB;gocreate table tbA(id int identity(1,1),column2 int, value float);create table tbB(id int, avgValue float);goinsert tbA(column2, value)select null,9 union allselect null,19 union allselect null,7 union allselect null,6 union allselect null,10 union allselect null,5 union allselect null,0 union allselect null,6 union allselect null,8 union allselect null,10 union allselect null,2 union allselect null,4 union allselect null,22 union allselect null,12 union allselect 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 awhere id <= (select MAX(id) from tbA) - 4;select * from tbB;drop table tbA;drop table tbB;/*-- tbB 结果：id avgValue----------- ----------------------1 10.22 9.43 5.64 5.45 5.86 5.87 5.28 69 9.210 1011 8.4*/
[解决办法]
--求每五行的平均值：--顺带做了一个效率测试-->>>测试数据:goif OBJECT_ID('tbl')is not nulldrop table tblgocreate table tbl(id int identity(1,1),name varchar(1) default 'a',value float)godeclare @a intset @a=1while @a<=100000begininsert tblselect null,9 union allselect null,19 union allselect null,7 union allselect null,6 union allselect null,10 union allselect null,5 union allselect null,0 union allselect null,6 union allselect null,8 union allselect null,10 union allselect null,2 union allselect null,4 union allselect null,22 union allselect null,12 union allselect null,2set @a=@a+1endselect * from tblgocreate table #tbl(value float)--方法1insert into #tblselect (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(效率很差)goif OBJECT_ID('pro_tracy')is not nulldrop proc pro_tracygocreate proc pro_tracy asdeclare @startid int,@endid intset @startid=1set @endid=5while @endid<=(select MAX(id) from tbl)begininsert #tblselect AVG(value) from tbl where id between @startid and @endidset @startid=@startid+1set @endid=@endid+1endgoexec pro_tracy--运行63s,结果79行数据goselect * from #tbl
[解决办法]
--求每五行的平均值：--顺带做了一个效率测试-->>>测试数据:goif OBJECT_ID('tbl')is not nulldrop table tblgocreate table tbl(id int identity(1,1),name varchar(1) default 'a',value float)godeclare @a intset @a=1while @a<=100000begininsert tblselect null,9 union allselect null,19 union allselect null,7 union allselect null,6 union allselect null,10 union allselect null,5 union allselect null,0 union allselect null,6 union allselect null,8 union allselect null,10 union allselect null,2 union allselect null,4 union allselect null,22 union allselect null,12 union allselect null,2set @a=@a+1endselect * from tblgocreate table #tbl(value float)--方法1insert into #tblselect (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(效率很差)goif OBJECT_ID('pro_tracy')is not nulldrop proc pro_tracygocreate proc pro_tracy asdeclare @startid int,@endid intset @startid=1set @endid=5while @endid<=(select MAX(id) from tbl)begininsert #tblselect AVG(value) from tbl where id between @startid and @endidset @startid=@startid+1set @endid=@endid+1end--加上索引gocreate unique index id_tbl_dex on tbl(id)--17Screate clustered index id_tbl_dex on tbl(id)--29screate nonclustered index id_tbl_dex on tbl(id)--26sdrop index id_tbl_dex on tblgo--方法1insert into #tblselect (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)goexec pro_tracy--63s,316行数据--经过上面的测试,最优方案是直接的查询语句加上unique索引goselect * from #tbl