两个表 求SQL解决办法

两个表 求SQL
表1
UserId Capital
1 50
2 60
3 70

表2
UserId Price Vol
1 1.8 50
1 3.6 20
2 2.4 70
3 6.2 10
2 1.3 30

表2要按UserId 去更新表1的Capital。。。。(Capital = Price * Vol)

[解决办法]

SQL code

create table 表1(UserID int,Capital money)create table 表2(UserID int,Price money,Vol int)goinsert 表1select 1,50 unionselect 2,60 unionselect 3,70insert 表2select 1,1.8,50 unionselect 1,3.6,20 unionselect 2,2.4,70 unionselect 3,6.2,10 unionselect 2,1.3,30 update 表1 set Capital=表1.Capital+b.Capital from (select UserID,SUM(Price*Vol) Capital from 表2 group by UserID)b where 表1.UserID=b.UserID  select * from 表1 /* UserID    Capital1    212.002    267.003    132.00*/drop table 表1drop table 表2