存储过程 建立临时表 通过循环

DROP PROCEDURE IF EXISTS `weather_report_cities`$$

CREATE DEFINER=`epimap`@`%` PROCEDURE `weather_report_cities`(
IN p_citiesorurls VARCHAR(21845) ,
IN p_city_count INT,
OUT p_count INT
)
BEGIN
   DECLARE v_i INT  DEFAULT 1;
   DECLARE v_length INT;
   DECLARE v_city VARCHAR(20);
  -- set @v_city = v_city;
  SET v_length = p_city_count;
   TRUNCATE epimap.ep_weather_report_temp;
   TRUNCATE epimap.`ep_weather_sk_temperature_temp`;
  -- truncate epimap.test_city;
-- SET SQL_SAFE_UPDATES=0; -- so slow on large talbe data using Innodb ,cause write data to log
-- delete from epimap.ep_weather_report_temp;
-- SET SQL_SAFE_UPDATES=1;
WHILE v_i<=v_length DO
   
   SELECT func_splitString(p_citiesorurls, ',', v_i) INTO v_city;   
    SET v_city = TRIM(v_city);
  --  insert into test_city values(v_city);
    INSERT INTO epimap.ep_weather_report_temp
(city,city_en,date_y,WEEK,cityid,temp1,temp2,temp3,temp4,temp5,temp6,weather1,weather2,weather3,weather4,weather5,weather6,wind1,wind2,wind3,wind4,wind5,wind6,fl1,fl2,fl3,fl4,fl5,fl6,ind,index_xs,index_uv,index_xc,index_tr,index_co,index_cl,index_ls,index_ag,create_time
)
( SELECT * FROM
((SELECT city,city_en,date_y,WEEK,cityid,temp1,temp2,temp3,temp4,temp5,temp6,weather1,weather2,weather3,weather4,weather5,weather6,wind1,wind2,wind3,wind4,wind5,wind6,fl1,fl2,fl3,fl4,fl5,fl6,ind,index_xs,index_uv,index_xc,index_tr,index_co,index_cl,index_ls,index_ag,create_time FROM epimap.ep_weather_report
WHERE city = v_city    ORDER BY create_time DESC LIMIT 1)
UNION
(SELECT city,city_en,date_y,WEEK,cityid,temp1,temp2,temp3,temp4,temp5,temp6,weather1,weather2,weather3,weather4,weather5,weather6,wind1,wind2,wind3,wind4,wind5,wind6,fl1,fl2,fl3,fl4,fl5,fl6,ind,index_xs,index_uv,index_xc,index_tr,index_co,index_cl,index_ls,index_ag,create_time FROM epimap.ep_weather_report
 WHERE cityid = v_city    ORDER BY create_time DESC LIMIT 1)
) AS new_ep_weather_report_temp
) ;

 INSERT INTO epimap.`ep_weather_sk_temperature_temp`
(city,wse,wd,ws,TIME)
    (SELECT * FROM    
    ((SELECT city,wse,wd,ws,TIME FROM epimap.`ep_weather_sk_temperature` WHERE city = v_city ORDER BY create_time DESC LIMIT 1)
    UNION
    (SELECT city,wse,wd,ws,TIME FROM epimap.`ep_weather_sk_temperature` WHERE cityid = v_city ORDER BY create_time DESC LIMIT 1)
    )AS new_weather_sk_temp);

  SET v_i=v_i+1;
END WHILE;
  SELECT COUNT(*) INTO p_count FROM  epimap.ep_weather_report_temp;
  SELECT a.city,cityid AS CODE,temp1 AS temp,weather1 AS weather0,
  CONCAT(wse,'℃') AS felltemp,wd AS wind,ws AS windspeed,CONCAT('今天',CONCAT(TIME,'发布')) AS realtime
 FROM epimap.ep_weather_report_temp AS a LEFT JOIN epimap.`ep_weather_sk_temperature_temp` AS b
ON a.city = b.city
;

END$$

即得到两张表结合后的结果，注意看