DELIMITER $$USE `topsale`$$DROP PROCEDURE IF EXISTS `sale_proce`$$CREATE DEFINER=`root`@`%` PROCEDURE `sale_proce`(IN countryList VARCHAR(200) ,IN beg VARCHAR(30),IN endd VARCHAR(30))BEGINDELETE  FROM sale_record WHERE country_id IN(countryList) AND datadate BETWEEN beg AND endd;INSERT INTO sale_Record(branch_model,hq_model,line,size,spec,country_id,center,country,datadate,saleQty)SELECT t.branch_model,t.hq_model,t.product_line,t.size,t.PRODUCT_SPEC_ID,pa.`COUNTRY_ID`,center.PARTY_NAME center,coun.`PARTY_NAME` country, DATE_FORMAT(t.`datadate`,'%Y-%m-01') DATE,COALESCE(SUM(t.`h_quantity`),0) saleQty FROM vive_tv_sale tJOIN shop_info si ON si.`SHOP_ID`=t.`shop_id`  AND si.flag=0JOIN party pa ON pa.`PARTY_ID`=si.`PARTY_ID` AND pa.flag=0JOIN (SELECT * FROM party) coun ON coun.party_id=pa.country_idJOIN (SELECT * FROM party) center ON center.party_id=coun.PARENT_PARTY_IDWHERE pa.country_id IN(countryList)AND  t.datadate BETWEEN beg AND enddGROUP BY pa.`COUNTRY_ID`,t.model,DATE_FORMAT(t.`datadate`,'%Y%m')ORDER BY center.PARTY_NAME,coun.`PARTY_NAME`,DATE_FORMAT(t.`datadate`,'%Y%m');    END$$DELIMITER ;

　　　　　UPDATE vive_tv_sale vts,        (SELECT a.id, pa.`COUNTRY_ID`,pa.`PARTY_ID`,si.`SHOP_ID`,tm.`hq_model`,tm.`branch_model`,        pr.`product_line`,pr.`size`,pr.`PRODUCT_SPEC_ID`        ,co.all_products_coefficient coeff        ,'',si.`CUSTOMER_ID`                     FROM  vive_tv_sale a,shop_info si,party pa,t_modelmap tm,product pr ,`coefficient` co         WHERE a.shop_id=si.shop_id         AND a.model=tm.branch_model         AND si.`PARTY_ID` = pa.`PARTY_ID`           AND co.`country` = pa.`COUNTRY_ID`          AND tm.`party_id` = pa.`COUNTRY_ID`          AND pr.`head_type_id` = 1          AND a.country=#{country}        AND a.datadate BETWEEN  #{beginDate} AND #{endDate}         AND tm.`hq_model` = pr.`PRODUCT_model`             AND si.flag = 0         AND pa.flag = 0         AND tm.flag = 0         AND pr.flag = 0) v_sl                 SET         vts.`country_id` = v_sl.COUNTRY_ID        ,vts.`party_id` = v_sl.PARTY_ID        ,vts.`hq_model` = v_sl.hq_model        ,vts.`branch_model` = v_sl.branch_model        ,vts.`product_line` = v_sl.product_line        ,vts.`size` = v_sl.size        ,vts.`PRODUCT_SPEC_ID` = v_sl.PRODUCT_SPEC_ID        ,vts.`coe_quan` = vts.`quantity`  / v_sl.coeff        ,vts.`coe_amou` = vts.`amount`  / v_sl.coeff        ,vts.`h_coe_quan` = vts.`quantity` /v_sl.coeff        ,vts.`h_coe_amou` = vts.h_amount /v_sl.coeff        ,vts.`customer_id` = v_sl.CUSTOMER_ID        ,vts.`country` = v_sl.COUNTRY_ID        ,vts.`flag`=0        WHERE flag=1        AND v_sl.id=vts.id        AND vts.country=#{country}        AND vts.datadate BETWEEN #{beginDate} AND #{endDate}