表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