4. 有两张关联的表A、B
A: id acount pay_id B: pay_id B_acount
1 100 1 1 0
2 200 2 2 0
3 200 1
要求,更新表B,使得B_acount的值等于表A中对应的pay_id的所有acount之和。打印如下:
B: pay_id B_acount
1 300
2 200
写了两种答案,不知道效率怎么样
第一种:
update
b,
(select pay_id c_pay_id, sum(account) c_sum
from a
group by pay_id) c
set b.b_account = c.c_sum
where b.pay_id = c.c_pay_id
第二种:
update b
set b.b_account = (select sum(a.account) from a where a.PAY_ID = b.pay_id)