sql语句实现：根据B表的汇总结果更新A表里的数据，A.username=B.username
如：
TableA username是唯一的
id username income
1 user1 20
2 user2 20
3 user3 30
4 user4 40
5 user5 50
TableB
id username incomeMonth
1 user1 20
2 user1 20
3 user1 30
4 user3 40
5 user3 50
6 user3 40
7 user3 50
我想实现，
TableB根据username汇总的incomeMonth
更新到TableA里相应的income里，
两表有这样的关系
TableB.username=TableA.username
------解决方案--------------------
update a set income = b.sumInMonth
from TableA a
join (select username ,sum(incomeMonth) as sumInMonth from TableB group by username ) b on a.username = b.username
------解决方案--------------------
update TableA
set incomemonth = b.incomemonth
from tableA a,(select username , sum(incomemonth) as incomemonth from tableb group by username) b
where a.username = b.username
------解决方案--------------------
create table TableA(
id int identity(1,1),
username varchar(10),
income int
)
insert TableA
select 'user1 ',20 union all
select 'user2 ',20 union all
select 'user3 ',30 union all
select 'user4 ',40 union all
select 'user5 ',50
create table TableB(
id int identity(1,1),
username varchar(10),
incomeMonth int
)
insert TableB
select 'user1 ',20 union all
select 'user1 ',20 union all
select 'user1 ',30 union all
select 'user3 ',40 union all
select 'user3 ',50 union all
select 'user3 ',40 union all
select 'user3 ',50
我没事把表建好测试了一下，xiaoku(野蛮人(^v^)) 和dawugui(潇洒老乌龟)的都正确，
老乌龟的有个笔误 set incomemonth = b.incomemonth 应该是
set income = b.incomemonth,呵呵
------解决方案--------------------
create table TableA(id int,username varchar(10),income int)
insert TableA select 1, 'user1 ', 20
union all select 2, 'user2 ', 20
union all select 3, 'user3 ', 30
union all select 4, 'user4 ', 40
union all select 5, 'user5 ', 50
create table TableB(id int,username varchar(10),incomeMonth int)
insert TableB select 1, 'user1 ', 20
union all select 2, 'user1 ', 20
union all select 3, 'user1 ', 30
union all select 4, 'user3 ', 40
union all select 5, 'user3 ', 50
union all select 6, 'user3 ', 40
union all select 7, 'user3 ', 50
--就是不一样
update tablea
set income=isnull((select sum(incomeMonth) from tableb b where b.username=a.username),0)
FROM tablea a, tableb b