合并表，两表行数不一样

[jronald]

表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把表二加进去，总是不对，要怎么写

[dawugui]

create table A(ID int,VALUE int)
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.id

drop 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 行）

*/

[-狙击手-]

set nocount on
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
*/

[xxsoft2007]

--测试数据准备

--表一
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

[jronald]

怎么把UPDATE和SELECT联系起来？

[-狙击手-]

--try :

set nocount on
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
*/

[-狙击手-]

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
Into New_table

from @t a 

[jronald]

但是后面还有avg10等，不能再用select into了啊，我把这些都写在select里面试试，主要是太长了，如果可以分成多个update，代码就好管理一些