多表联合操作

来源：互联网发布：64位sql安装提示错误编辑：程序博客网时间：2024/06/10 09:25

有如下三张表:
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_valuefrom (select b.fund_code,conf.* from fund_asset_conf conf,fund_gen_info bwhere conf.fund_id=b.fund_id and b.fund_code in(select fundCode from fund_forsale) order by enddate desc ) agroup 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

0 0