两表合并比较优化
我想合并两张表,上次数据A和这次数据B一样的标记为N,A表有B表没有,就插入B表,标记为X,A表没有B表有,就标记为Y新表就是A的表的X+B表的N和Y,并需要有标记
程序代码:
UPDATE tbl_old SET 变动='' UPDATE tbl_new SET 变动='' update c set c.变更='N' from tbl_new c,tbl_old d where c.产品=d.产品 AND c.国家=d.国家 and c.商户=d.商户 and c.批次=d.批次 and (ABS(c.金额-d.金额))<=0.1 AND c.进货日期=d.进货日期 AND c.货币='RMB' AND d.货币='RMB' update d set d.变更='N' from tbl_new c,tbl_old d where c.产品=d.产品 AND c.国家=d.国家 and c.商户=d.商户 and c.批次=d.批次 and (ABS(c.金额-d.金额))<=0.1 AND c.进货日期=d.进货日期 AND c.货币='RMB' AND d.货币='RMB' UPDATE tbl_old SET 变动='X' WHERE 变更<>'N' UPDATE tbl_new SET 变动='Y' WHERE 变更<>'N' insert into tbl select * from (select * from tbl_old where 变更='X' union all selelct * from tbl_new) a
数据量有几十万条,就很慢,我用merge好像也是很慢
程序代码:
MERGE INTO tbl_new c USING tbl_old d ON c.产品=d.产品 AND c.国家=d.国家 and c.商户=d.商户 and c.批次=d.批次 and (ABS(c.金额-d.金额))<=0.1 AND c.进货日期=d.进货日期 AND c.货币='RMB' AND d.货币='RMB' WHEN MATCHED THEN UPDATE SET c.变动='N' WHEN NOT MATCHED THEN INSERT VALUES(d.[ ] ,d.产品,d.国家 ,d.商户,d.批次,d.进货日期,d.货币,d.金额,d.变动);
不知道大神有没有优化的方法,谢谢了
[此贴子已经被作者于2017-11-1 23:34编辑过]