多表联合操作

有如下三张表:
fund_forsale

fundCode（PRIMARY KEY）	market_value
000706	NULL
000707	NULL
000708	NULL

fund_gen_info

fund_code	fund_id
000706	1
000707	2
000708	3
000709	4

fund_asset_conf

fund_id	asset_value	enddate
1	0.12	12345678
1	0.13	12345679
1	0.01	12345673
2	0.23	12345675
2	0.25	12345673
3	0.31	12345671
4	0.41	12345670

表fund_forsale更新方式为：选取表fund_asset_conf中对应最新（enddate倒序）基金的asset_conf字段

有以下两种方法

insert into fund_forsale (fundCode,market_value)
( select fund_code as fundCode,asset_nav as market_value
from (select b.fund_code,conf.* 
from fund_asset_conf conf,fund_gen_info b
where conf.fund_id=b.fund_id and b.fund_code in
(select fundCode from fund_forsale) 
order by enddate desc ) a
group by fund_code )
on duplicate key update market_value=values(market_value);

运行时间6.97s

update fund_forsale as c 
inner join 
(select fund_code, asset_nav 
from fund_gen_info natural join 
(select fund_id, asset_nav 
from fund_asset_conf natural join 
(select fund_id, max(enddate) as enddate 
from fund_asset_conf 
group by fund_id) as t1) 
as t2) b 
on b.fund_code=c.fundCode 
set c.market_value=b.asset_nav;

运行时间0.15s