DELIMITER $$
USE `mtapp_toms`$$
DROP PROCEDURE IF EXISTS `proc_dailyrpt_data_mix_preprocess`$$
CREATE DEFINER=`dtauser`@`%` PROCEDURE `proc_dailyrpt_data_mix_preprocess`()
BEGIN
-- CALL proc_weekrpt_data_mix_preprocess()
#declare v_sql varchar(1000);
DECLARE v_col VARCHAR(50);
DECLARE v_city VARCHAR(50);
DECLARE v_date VARCHAR(20);
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR
SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_name = 'tb_dailyrpt_data_index_day'
AND COLUMN_NAME NOT IN ('时间','所属省份','所属地市','地方简称') ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET v_date='2020-07-12';#(SELECT DATE_ADD( CURRENT_DATE(),INTERVAL -1 DAY));
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_col;
IF done THEN
LEAVE read_loop;
END IF;
BEGIN
DECLARE done2 INT DEFAULT FALSE;
DECLARE cur2 CURSOR FOR
SELECT 地市名称 FROM `tb_dailyrpt_config_city` ;#where 地市名称='全量';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = TRUE;
OPEN cur2;
read_loop2: LOOP
FETCH cur2 INTO v_city;
IF done2 THEN
LEAVE read_loop2;
END IF;
BEGIN
#declare v_val varchar(100);
SET @v_sql=CONCAT('set @v_val=(select ',v_col,' from tb_dailyrpt_data_index_day WHERE 所属地市=''',v_city,''' AND 时间=''',v_date,''' limit 1)');
PREPARE stmt FROM @v_sql;
EXECUTE stmt;
#select @v_val;
IF (@v_val IS NULL) THEN
SET @v_sql=CONCAT('set @v_val_last=(SELECT `',v_col,'` FROM `tb_dailyrpt_data_index_day`
WHERE 所属地市=''',v_city,''' AND 时间
IS NOT NULL ORDER BY `时间` DESC LIMIT 1)');
#set @sql=v_sql
#select @v_sql;
PREPARE stmt FROM @v_sql;
EXECUTE stmt;
IF(@v_val_last IS NOT NULL) THEN
SET @v_sql=CONCAT('update tb_dailyrpt_data_index_day set ',v_col,'=''',@v_val_last,''' WHERE 所属地市=''',v_city,''' AND 时间=''',v_date,''' ');
#SELECT @v_sql;
PREPARE stmt FROM @v_sql;
EXECUTE stmt;
END IF;
END IF;
END;
END LOOP;
CLOSE cur2;
#select v_col;
END;
END LOOP;
CLOSE cur;
END$$
DELIMITER ;