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

# 求助，关于表批量更新的基础问题

kmlzkma 2008-01-09 11:04:55
a表：
id hits
1 0
2 0
3 0
.....

b表
id hits
1 1
3 5
......

c表
id hits
1 10
2 25
......

b，c表是a表子集，有很多的类似b,c的表，a表是个汇总，是其它所有表的一个总和。a表大约10万条记录，其它表记录数略少点。

...全文
51 点赞 收藏 7

7 条回复

changjiangzhibin 2008-01-09
study

kmlzkma 2008-01-09

wzy_love_sly 2008-01-09
``````declare @a table (id int,hits int)
insert into @a select 1,0
insert into @a select 2,0
insert into @a select 3,0

declare @b table (id int,hits int)
insert into @b select 1,3
insert into @b select 3,5

declare @c table (id int,hits int)
insert into @c select 1,10
insert into @c select 2,25

update   @a   set   hits=
(select isnull(sum(hits),0) from @b where id=a.id)+
(select isnull(sum(hits),0)  from @c where id=a.id)
from   @a a

select * from @a``````

id hits
1 13
2 25
3 5

dobear_0922 2008-01-09
``````update a
set hits = (select sum(hits) from b表 where id = a.id) + (select sum(hits) from c表 where id = a.id)
from a表 a``````

kk19840210 2008-01-09
``````create table #a (id int,hits int)

create table #b (id int,hits int)

create table #c (id int,hits int)

insert into #a values(1,0)
insert into #a values(2,0)
insert into #a values(3,0)

insert into #b values(1,1)

insert into #b values(3,5)

insert into #c values(1,10)
insert into #c values(2,25)

update #a set hits=a.hits from #a inner join (select id,hits=sum(hits) from (select * from #b union all select * from #c) b group by b.id) a on #a.id=a.id

select * from #a

id          hits
----------- -----------
1           11
2           25
3           5

(3 行受影响)
``````

wzy_love_sly 2008-01-09
update a set hits=sum(b.hits)+sum(c.hits)
from a left join b on a.id=b.id
left join c on a.id=c.id
group by a.id

-狙击手- 2008-01-09
``````update a
set hits = (select sum(hits) from b表 where id = a.id) +
(select sum(hits) from c表 where id = a.id)
from a表 a``````

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区