卡信息处理流程卡信息的查询按钮调用startCARDTotal总函数!总函数中分别循环调用三个子函数card1_temp,card2_temp,card3_temp
CREATE DEFINER=`root`@`%` PROCEDURE `startCARDTotal`(in produc VARCHAR(30),in stime datetime,in etime datetime)
BEGIN
DECLARE i_1 INT DEFAULT 0;
DECLARE i_2 INT DEFAULT 0;
DECLARE i_3 INT DEFAULT 0;
DECLARE card1num INT;
DECLARE card2num INT;
DECLARE card3num INT;
DECLARE card VARCHAR(30);
-- 如果存在临时表则删除
DROP TABLE IF EXISTS card1_temp;
DROP TABLE IF EXISTS card2_temp;
DROP TABLE IF EXISTS card3_temp;
-- 复制整张表
CREATE TABLE card1_temp LIKE result_total;
CREATE TABLE card2_temp LIKE result_total;
CREATE TABLE card3_temp LIKE result_total;
-- 删除多余列
ALTER TABLE card1_temp DROP SERIAL,DROP Production,DROP MODEL,DROP CARD_2,DROP CARD_3;
ALTER TABLE card2_temp DROP SERIAL,DROP Production,DROP MODEL,DROP CARD_1,DROP CARD_3;
ALTER TABLE card3_temp DROP SERIAL,DROP Production,DROP MODEL,DROP CARD_1,DROP CARD_2;
-- 统计多少张不同的卡
SELECT COUNT(CARD_1) INTO card1num FROM(SELECT DISTINCT CARD_1 FROM result_total) AS tmp1;
SELECT COUNT(CARD_2) INTO card2num FROM(SELECT DISTINCT CARD_2 FROM result_total) AS tmp2;
SELECT COUNT(CARD_3) INTO card3num FROM(SELECT DISTINCT CARD_3 FROM result_total) AS tmp3;
-- 三张卡分别进行循环统计并插入
WHILE i_1<card1num
DO
-- 查询result_total得到卡号
SELECT CARD_1 INTO card FROM
(SELECT DISTINCT CARD_1 FROM result_total WHERE NewsTime BETWEEN stime AND etime)
AS card1 LIMIT i_1,1;
-- 调用card_temp插入统计结果
CALL card1_temp(card,produc,stime,etime);
SET i_1=i_1+1;
END WHILE;
-- 三张卡分别进行循环统计并插入
WHILE i_2<card2num
DO
-- 查询result_total得到卡号
SELECT CARD_2 INTO card FROM
(SELECT DISTINCT CARD_2 FROM result_total WHERE NewsTime BETWEEN stime AND etime)
AS card2 LIMIT i_2,1;
-- 调用card_temp插入统计结果
CALL card2_temp(card,produc,stime,etime);
SET i_2=i_2+1;
END WHILE;
-- 三张卡分别进行循环统计并插入
WHILE i_3<card3num
DO
-- 查询result_total得到卡号
SELECT CARD_3 INTO card FROM
(SELECT DISTINCT CARD_3 FROM result_total WHERE NewsTime BETWEEN stime AND etime)
AS card3 LIMIT i_3,1;
-- 调用card_temp插入统计结果
CALL card3_temp(card,produc,stime,etime);
SET i_3=i_3+1;
END WHILE;
END