求一条update语句

Question

表一:members
id     user_name   recharge（充值）    withdrawals（提现）   balance（余额）
1          张三                        0                                  0                                    0
2          李四                        0                                 0                                       0 
3          王五                        0                                     0                                  0
4          小刘                       0                                   0                                     0表二：money
id         uid       moneylog   type
1          1               100             1
2          2               500             1
3          4                200             1
4          2              200              0
5          1              300              1
6          4              150              0
7          1               180             0
8          2               20               0
说明：members.id与money.uid关联，money 表的type字段1代表充值，0代表提现
如何用一条语句更新表一的recharge,withdrawals,balance三个字段实现
id     user_name   recharge（充值）    withdrawals（提现）   balance（余额）
1          张三                    400                                 180                            220
2          李四                     500                                220                            280
3          王五                     0                                         0                                  0
 4          小刘                    200                                150                              50

Answer 1

分别执行以下三个语句update members A inner join (select uid, sum(moneylog) slog, type from money group by uid,type) B on A.id = B.uid  
set A.recharge = B.slog where type = 1;update members A inner join (select uid, sum(moneylog) slog, type from money group by uid,type) B on A.id = B.uid  
set A.withdrawals = B.slog where type = 0 ;update members set balance  = recharge - withdrawals;

Answer 2

update members m inner join (
select uid,
sum(if(type=1,moneylog,0)) as recharge,
sum(if(type=1,moneylog,0)) as withdrawals
from money
group by uid
) b on m.id=b.uid
set m.recharge=b.recharge,
m.withdrawals=b.withdrawals,
m.balance=b.recharge-b.withdrawals

Answer 3

update members m,
(
select uid,sum(case when type = 1 then moneylog else 0 end)recharge,sum(case when type = 0 then moneylog else 0 end)withdrawals from money group by uid
)n
set m.recharge = n.recharge,m.withdrawals = n.withdrawals,m.balance = n.recharge - n.withdrawals
where m.id = n.uid

Answer 4

update members a inner join (
    select uid,sum(if(type=1,moneylog,0)) as a1, sum(if(type=1,moneylog,0)) as a2
    from money   group by uid) b on a.id=b.uid
set a.recharge=b.a1,a.withdrawals=b.a2,a.balance=b.a1-b.a2