BEGIN
-- 定义变量
-- 定义done
DECLARE done INT;
-- 定义 ammeter_id_bl
DECLARE ammeter_id_bl DOUBLE;
-- 定义表名（tableName）游标
DECLARE rs_ammeter_id CURSOR FOR
-- 得到游标集合
SELECT id FROM `res_meter` WHERE id<>1 AND id<>10 AND meter_type=1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
-- 初始化done，为0，false
SET done = 0;
-- 打开游标
OPEN rs_ammeter_id;
-- 遍历游标（开始循环）
REPEAT
FETCH rs_ammeter_id into ammeter_id_bl;
IF done<>1 then
/*
1、处理要插入的数据
*/
-- 给变量赋值
SET @ammeter_id_bl=ammeter_id_bl;
SELECT t.*
INTO
@min_meter_count_top
,@min_meter_count_max
,@min_meter_count_avg
,@min_meter_count_min
,@add_top
,@add_max
,@add_avg
,@add_min
,@collect_time
,@meter_status
FROM
(
SELECT
meter_count_top
,meter_count_max
,meter_count_avg
,meter_count_min
,ROUND(RAND()*10+4,2) add_top
,ROUND(RAND()*8+3,2) add_max
,ROUND(RAND()*7+2,2) add_avg
,ROUND(RAND()*6+1,2) add_min
,DATE_ADD(collect_time, INTERVAL 30 MINUTE) collect_time
,ROUND(RAND(),0) meter_status
FROM
cap_ammeter_201810
WHERE
collect_time = (
SELECT
MAX(collect_time) collect_time
FROM
cap_ammeter_201810
WHERE
ammeter_id = @ammeter_id_bl
GROUP BY
ammeter_id
)
AND ammeter_id = @ammeter_id_bl
)t;
SET @meter_count_top=ROUND(@min_meter_count_top+@add_top,2)
,@meter_count_max=ROUND(@min_meter_count_max+@add_max,2)
,@meter_count_avg=ROUND(@min_meter_count_avg+@add_avg,2)
,@meter_count_min=ROUND(@min_meter_count_min+@add_min,2);
SET @meter_count=ROUND(@meter_count_top+@meter_count_max+@meter_count_avg+@meter_count_min,2);
-- 查看变量的值
/*
SELECT
@ammeter_id_bl
,@collect_time
,@meter_status
,@min_meter_count_top
,@min_meter_count_max
,@min_meter_count_avg
,@min_meter_count_min
,@add_top
,@add_max
,@add_avg
,@add_min
,@meter_count_top
,@meter_count_max
,@meter_count_avg
,@meter_count_min;
*/
/*
2、插入数据
*/
-- 1）写sql语句 如果要用到变量，使用CONCAT()拼接
-- 查询出要插入的数据
SET @queryDataSqlStr=CONCAT("SELECT
",ROUND(@meter_count,2)," meter_count
,'",@collect_time,"' collect_time
,",@ammeter_id_bl," ammeter_id
,",@meter_status," meter_status
,",ROUND(@meter_count_top,2)," meter_count_top
,",ROUND(@meter_count_max,2)," meter_count_max
,",ROUND(@meter_count_avg,2)," meter_count_avg
,",ROUND(@meter_count_min,2)," meter_count_min"
);
-- 插入数据
SET @insertSqlStr=CONCAT("INSERT INTO cap_ammeter_201810
(
meter_count
,collect_time
,ammeter_id
,meter_status
,meter_count_top
,meter_count_max
,meter_count_avg
,meter_count_min
) ",@queryDataSqlStr
);
-- 4）查看sql语句
-- SELECT @insertSqlStr;
-- 3）执行sql语句
PREPARE insertSqlStr FROM @insertSqlStr;
EXECUTE insertSqlStr;
END IF;
-- 直到done变为true结束循环
UNTIL done END
REPEAT;
CLOSE rs_ammeter_id;
END
BEGIN
-- 定义变量
DECLARE i INT DEFAULT 10;
-- 开始循环
WHILE i<13
DO
SET @day_bl=i;
SET @createSqlStr=CONCAT("CREATE TABLE cap_ammeter_2017",@day_bl,"
(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`meter_count` double(14,2) DEFAULT NULL COMMENT '电表读数',
`collect_time` datetime DEFAULT NULL COMMENT '采集时间',
`ammeter_id` bigint(20) NOT NULL COMMENT '电表id，对应res_meter的id',
`meter_status` int(1) NOT NULL COMMENT '电表状态 0：正常；1：异常',
`meter_count_top` double(14,2) DEFAULT NULL COMMENT '尖值电量',
`meter_count_max` double(14,2) DEFAULT NULL COMMENT '峰值电量',
`meter_count_avg` double(14,2) DEFAULT NULL COMMENT '平值电量',
`meter_count_min` double(14,2) DEFAULT NULL COMMENT '谷值电量',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19576 DEFAULT CHARSET=utf8;");
-- 查看sql
-- SELECT @createSqlStr;
-- 运行sql
PREPARE createSqlStr FROM @createSqlStr;
EXECUTE createSqlStr;
SET i=i+1;
END WHILE;
COMMIT;
END
