导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

合并表,两表行数不一样

jronald 2007-11-28 03:25:58
表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把表二加进去,总是不对,要怎么写
...全文
91 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过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 行)

*/
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告