3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
合并表，两表行数不一样
表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把表二加进去，总是不对，要怎么写
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
jronald 2007-11-28
但是后面还有avg10等，不能再用select into了啊，我把这些都写在select里面试试，主要是太长了，如果可以分成多个update，代码就好管理一些
-狙击手- 2007-11-28
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 -狙击手- 2007-11-28
--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
*/
jronald 2007-11-28
怎么把UPDATE和SELECT联系起来？
xxsoft2007 2007-11-28
--测试数据准备
--表一
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
-狙击手- 2007-11-28
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
*/dawugui 2007-11-28
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 行）
*/