本文共 1945 字,大约阅读时间需要 6 分钟。
delimiter $$ drop procedure if exists P_insert_sale_order_detail_3 $$ create procedure P_insert_sale_order_detail_3(out p_num int,out p_num2 int) BEGIN select @p_num:=min(order_detail_id),@p_num2:=max(order_detail_id) from sale_order_detail ; set p_num=@p_num; set p_num2=@p_num2; select @p_num3:=max(order_detail_id) from sale_order_detail_3 ; if @p_num3>p_num then set p_num=@p_num3; end if ; WHILE (p_num<=p_num2) do INSERT INTO sale_order_detail_3 ( order_detail_id ,erp_order_detail_id ,order_id ,erp_order_id ,shop_id ,member_id ,create_time ,place_order_time ,pay_time ,sale_time ,outer_sku_id ,product_id ,erp_sku_id ,sku_id ,color_id ,size_id ,num ,update_timestamp ,network_price ,real_price ,cost_price ,network_amount ,real_amount ,discount_amount ,discount_rate ,avg_order_discount ,retained_amount ,post_fee ,is_presale ,shop_name ,member_name ,product_code ,color_name ,size_name ,sku_name ,outer_sku_name ,outer_order_id ,if_original ) SELECT b.order_detail_id ,b.erp_order_detail_id ,b.order_id ,b.erp_order_id ,b.shop_id ,b.member_id ,b.create_time ,b.place_order_time ,b.pay_time ,b.sale_time ,b.outer_sku_id ,b.product_id ,b.erp_sku_id ,b.sku_id ,b.color_id ,b.size_id ,b.num ,b.update_timestamp ,b.network_price ,b.real_price ,b.cost_price ,b.network_amount ,b.real_amount ,b.discount_amount ,b.discount_rate ,b.avg_order_discount ,b.retained_amount ,b.post_fee ,b.is_presale ,b.shop_name ,b.member_name ,b.product_code ,b.color_name ,b.size_name ,b.sku_name ,b.outer_sku_name ,b.outer_order_id ,b.if_original FROM ( SELECT order_detail_id FROM sale_order_detail WHERE order_detail_id>=p_num+1 ORDER BY order_detail_id ASC LIMIT 10000 ) a JOIN sale_order_detail b ON a.order_detail_id=b.order_detail_id ; SET p_num=p_num+10000; end while ; end ; $$ /* call P_insert_sale_order_detail_3(@p_num,@p_num2 ); select @p_num,@p_num2; select count(1),max(order_detail_id),count(distinct order_detail_id) from sale_order_detail_3; */转载地址:http://zmfmi.baihongyu.com/