mysql查询一个表的字段，添加或修改到另外一个表的数据

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))
BEGIN
DELETE  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 t
JOIN shop_info si ON si.`SHOP_ID`=t.`shop_id`  AND si.flag=0
JOIN party pa ON pa.`PARTY_ID`=si.`PARTY_ID` AND pa.flag=0
JOIN (SELECT * FROM party) coun ON coun.party_id=pa.country_id
JOIN (SELECT * FROM party) center ON center.party_id=coun.PARENT_PARTY_ID
WHERE pa.country_id IN(countryList)
AND  t.datadate BETWEEN beg AND endd
GROUP 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}

 

转载于:https://www.cnblogs.com/Yusco/p/9772798.html