本文共 1835 字，大约阅读时间需要 6 分钟。

delimiter $$  create procedure p_vipshop_to_actual_day_shop_sale ( in p_shop_id int, in p_sale_date date, out p_update_insert varchar(50) ) #毛利率 begin  SELECT @margin:=sum(profit) / sum(sale_amt)  FROM ( SELECT DISTINCT tmp1.product_id, tmp1.sale_amt, tmp1.sale_num * t2.cost_price AS total_cost, t2.cost_price AS cost, tmp1.sale_amt - tmp1.sale_num * t2.cost_price AS profit FROM ( SELECT t1.product_id, sum(t1.total_sale_amount) AS sale_amt, sum(t1.total_sale_number) AS sale_num FROM order_vipshop_daily_detail_info t1 WHERE ( shop_id = p_shop_id AND sale_date = p_sale_date ) GROUP BY t1.product_id ) tmp1 JOIN l3_product_inherent_property_code t2 ON tmp1.product_id = t2.product_id ) tmp; #销售额，销量，件单价 SELECT @amt:= sum(total_sale_amount), @num:= sum(total_sale_number) , @price:= sum(total_sale_amount) / sum(total_sale_number) FROM order_vipshop_daily_detail_info WHERE ( shop_id = p_shop_id AND sale_date = p_sale_date ); # 写入数据更新字段 rate_of_margin,unit_price,place_num,place_amount if not exists( select 1 from actual_day_shop_sale where shop_id=p_shop_id and the_date=p_sale_date ) then    insert actual_day_shop_sale(the_date,shop_brand_id,`year`,`month`,`day`,shop_id,shop_name, brand_id,actual_sale,description,rate_of_margin,unit_price,place_num,place_amount) values( p_sale_date ,(SELECT shop_brand_id from base_shop_brand where shop_id=p_shop_id) ,year(p_sale_date) ,month(p_sale_date) ,day(p_sale_date) ,p_shop_id ,(select shop_name from base_shop where shop_id=p_shop_id) ,(SELECT brand_id from base_shop_brand where shop_id=p_shop_id) ,@amt ,'ETL Input' ,@margin ,@price ,@num ,@amt ); set p_update_insert='insert';   else   update actual_day_shop_sale  set rate_of_margin=@margin,place_amount=@amt,place_num=@num,unit_price=@price where shop_id=p_shop_id and the_date=p_sale_date; set p_update_insert='update';   end if ; end ; $$

转载地址：http://llfmi.baihongyu.com/