表1
ID VALUE
1 34
2 54
3 34
4 34
5 53
6 46
7 35
8 23
9 54
10 23
11 34
12 34表2
ID AVG5
0 表一1-5的平均值,下面以些类推,最后一组不到5个按5个算
1
2现在建一新表ID VALUE AVG5(空为NULL,#为数值)
1 34
2 54
3 34
4 34
5 53 #
6 46
7 35
8 23
9 54
10 23 #
11 34
12 34 #我用CREATE建了表,用INSERT INTO把表一加到新表,想用UPDATE把表二加进去,总是不对,要怎么写
ID VALUE
1 34
2 54
3 34
4 34
5 53
6 46
7 35
8 23
9 54
10 23
11 34
12 34表2
ID AVG5
0 表一1-5的平均值,下面以些类推,最后一组不到5个按5个算
1
2现在建一新表ID VALUE AVG5(空为NULL,#为数值)
1 34
2 54
3 34
4 34
5 53 #
6 46
7 35
8 23
9 54
10 23 #
11 34
12 34 #我用CREATE建了表,用INSERT INTO把表一加到新表,想用UPDATE把表二加进去,总是不对,要怎么写
insert into A values(1 , 34)
insert into A values(2 , 54)
insert into A values(3 , 34)
insert into A values(4 , 34)
insert into A values(5 , 53)
insert into A values(6 , 46)
insert into A values(7 , 35)
insert into A values(8 , 23)
insert into A values(9 , 54)
insert into A values(10, 23)
insert into A values(11, 34)
insert into A values(12, 34)
go
select A.* , t.value avg5 from A,
(select id = ((id + 4) / 5) , value = avg(value*1.0) from A group by ((id + 4) / 5)) t
where (A.id + 4) / 5 = t.iddrop table A/*
ID VALUE avg5
----------- ----------- ----------------------------------------
1 34 41.800000
2 54 41.800000
3 34 41.800000
4 34 41.800000
5 53 41.800000
6 46 36.200000
7 35 36.200000
8 23 36.200000
9 54 36.200000
10 23 36.200000
11 34 34.000000
12 34 34.000000(所影响的行数为 12 行)*/
declare @t table(ID int,VALUE int)
insert @t select 1 ,34
insert @t select 2 ,54
insert @t select 3 ,34
insert @t select 4 ,34
insert @t select 5 ,53
insert @t select 6 ,46
insert @t select 7,35
insert @t select 8 ,23
insert @t select 9,54
insert @t select 10 ,23
insert @t select 11 ,34
insert @t select 12 ,34
declare @b table(ID int,avg5 int)insert @b select 1 ,0
insert @b select 2 ,0
insert @b select 3,0--case when a.id%5 = 0 then
update @b
set avg5 = (select avg(value) from @t where id <= a.id * 5 and id > (a.id -1) * 5)
from @b a
select a.id as id1,value,(case when a.id%5 = 0 then (select avg5 from @b where id =a.id/5)
when a.id = (select max(id) from @t) and (id%5 != 0) then (select avg5 from @b where id = a.id/5 + 1)
else null end )as avg5
from @t a /*
id1 value avg5
----------- ----------- -----------
1 34 NULL
2 54 NULL
3 34 NULL
4 34 NULL
5 53 41
6 46 NULL
7 35 NULL
8 23 NULL
9 54 NULL
10 23 36
11 34 NULL
12 34 34
*/
create table #table1([id] int , value int)insert into #table1
values(1,34)
insert into #table1
values(2,54)
insert into #table1
values(3,34)
insert into #table1
values(4,34)
insert into #table1
values(5,53)
insert into #table1
values(6,46)
insert into #table1
values(7,35)
insert into #table1
values(8,23)
insert into #table1
values(9,54)
insert into #table1
values(10,23)
insert into #table1
values(11,34 )
insert into #table1
values(12,34 )
insert into #table1
values(13,34 )--select * From #table1--表二
select id , sum(value)/5 as avg5 into #table2
from
(select (id - 1)/5 as [id] , value
from #table1) a
group by id --select * From #table2
--表三
select * , null as avg5 into #table3
from #table1--select * from #table3--更新
update a set a.avg5 = b.avg5
from #table3 a , #table2 b
where (a.id - 1)/5 = b.id and (a.id % 5 = 0 or a.id = (select max(id) from #table3))select * from #table3
drop table #table1
drop table #table2
drop table #table3
declare @t table(ID int,VALUE int)
insert @t select 1 ,34
insert @t select 2 ,54
insert @t select 3 ,34
insert @t select 4 ,34
insert @t select 5 ,53
insert @t select 6 ,46
insert @t select 7,35
insert @t select 8 ,23
insert @t select 9,54
insert @t select 10 ,23
insert @t select 11 ,34
insert @t select 12 ,34
select * from @b
select a.id as id1,value,(case when a.id%5 = 0 then (select avg(value) from @t where id <=a.id and (id > a.id-5))
when a.id = (select max(id) from @t) and (id%5 != 0) then (select avg(value) from @t where id <= a.id and id > a.id/5 *5)
else null end )as avg5
from @t a /*
id1 value avg5
----------- ----------- -----------
1 34 NULL
2 54 NULL
3 34 NULL
4 34 NULL
5 53 41
6 46 NULL
7 35 NULL
8 23 NULL
9 54 NULL
10 23 36
11 34 NULL
12 34 34
*/
when a.id = (select max(id) from @t) and (id%5 != 0) then (select avg(value) from @t where id <= a.id and id > a.id/5 *5)
else null end )as avg5
Into New_tablefrom @t a