求教一个高效率的sql
用表B中的 net_amount 字段 更新表A中 net_amout 的数据.
两表的关联关系是B.quote_oid = A.oid
A与B表的关系是1：n
B表中取值的条件是 line_num 为最小的一条，并且status不等于40
参考数据如下：
A: oid net_amout
1 100
B: oid quote_oid line_num status net_amout
100 1 1 40 101
101 1 2 10 102
103 1 3 10 103
最终我们得到的结果应该为
A: OID NET_AMOUNT
1 102
[解决办法]
merge into a
using (select b.quote_oid,
b.net_amout,
row_number() over(partition by b.quote_oid order by line_num) rn
from b
where b.status <> 40) t
on (a.id = t.quote_oid and t.rn = 1)
when matched then
update set a. net_amount = t.net_amout;
[解决办法]
quote_oid + line_num + status
是否唯一？
update a
set net_amount =
(select net_amount
from b
where b.quote_oid = a.oid
and status <> 40
and line_num = (select min(line_num)
from b c
where c.quoto_oid = a.oid
and status <> 40))
