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

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

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万条记录,其它表记录数略少点。

现在要把其它表汇总到a表,请问如何写。
...全文
51 点赞 收藏 7
写回复
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
changjiangzhibin 2008-01-09
study
回复
kmlzkma 2008-01-09
回复1楼,报错,列不允许有空值,b表是a表的真子集,是不是因为a表有的id,b表中没有,所以报错。还有不明白为什么用sum(),id是主键,
回复
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
创建于2007-09-28

3.2w+

社区成员

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