mysql一次统计脚本

脚本1：

DELIMITER $$

USE `indonesia`$$

DROP PROCEDURE IF EXISTS `statisticalInvestigation`$$

CREATE DEFINER=`root`@`%` PROCEDURE `statisticalInvestigation`(IN p_date VARCHAR(7))
BEGIN
     
 
DECLARE v_date VARCHAR(7);
DECLARE v_date2 VARCHAR(7);
     
-- ----------------------------------------------------------------------------------------------------------

DROP TABLE IF EXISTS tmp_table;

CREATE TEMPORARY TABLE tmp_table (     
COL1 VARCHAR(35),     
COL2 VARCHAR(35),     
COL3 VARCHAR(35),     
COL4 VARCHAR(1)     
);

INSERT INTO tmp_table(COL1, COL2, COL3)
SELECT 'Electricity Meter quantity',CAST(COUNT(1) AS CHAR),'' FROM DA_BJ;

INSERT INTO tmp_table(COL1, COL2, COL3)
SELECT 'Purchased Electricity Meter quantity',CAST(COUNT(DISTINCT D.BJJH) AS CHAR),'' FROM DA_BJ D, ORDER_MASTER M WHERE D.BJJH = M.METERNO;

INSERT INTO tmp_table(COL1, COL2, COL3)
VALUES('','','');

INSERT INTO tmp_table(COL1, COL2, COL3)
VALUES('Date','Ele Meter purchase token Num','Ele Meter management token Num');


-- ------------------------------------
SET v_date = p_date;
WHILE v_date <= DATE_FORMAT(NOW(), '%Y-%m') DO
INSERT INTO tmp_table(COL1,COL4) VALUES(v_date,'1');
UPDATE tmp_table SET COL2 = (
SELECT CAST(COUNT(t.token) AS CHAR) FROM ORDER_TOKEN T, ORDER_MASTER M
  WHERE T.ORDERSID = M.ORDERSID 
  AND M.ORDER_TYPE IN ('01','23') 
  AND M.ORDER_TYPE NOT IN ('15','16')
  AND M.RES_TYPE = '03'
  AND DATE_FORMAT(M.op_time, '%Y-%m') = v_date
) WHERE COL1 = v_date AND COL4 = '1';
UPDATE tmp_table SET COL3 = (
SELECT CAST(COUNT(t.token) AS CHAR) FROM ORDER_TOKEN T, ORDER_MASTER M
  WHERE T.ORDERSID = M.ORDERSID
  AND M.ORDER_TYPE NOT IN ('01','23','15','16')
  AND M.RES_TYPE = '03'
  AND DATE_FORMAT(M.op_time, '%Y-%m') = v_date
) WHERE COL1 = v_date AND COL4 = '1';

SET v_date = DATE_FORMAT(DATE_ADD(DATE_FORMAT(CONCAT(v_date,'-01'), '%Y-%m-%d'), INTERVAL 1 MONTH), '%Y-%m');
END WHILE;
-- ------------------------------------

INSERT INTO tmp_table(COL1, COL2, COL3)
VALUES('','','');

INSERT INTO tmp_table(COL1, COL2, COL3)
SELECT 'Water Meter quantity',CAST(COUNT(1) AS CHAR),'' FROM DA_SB;

INSERT INTO tmp_table(COL1, COL2, COL3)
SELECT 'Purchased Water Meter quantity',CAST(COUNT(DISTINCT D.SBJH) AS CHAR),'' FROM DA_SB D, ORDER_MASTER M WHERE D.SBJH = M.METERNO;

INSERT INTO tmp_table(COL1, COL2, COL3)
VALUES('','','');

INSERT INTO tmp_table(COL1, COL2, COL3)
VALUES('Date','Water Meter purchase token Num','Water Meter management token Num');
-- ------------------------------------
SET v_date2 = p_date;
WHILE v_date2 <= DATE_FORMAT(NOW(), '%Y-%m') DO
INSERT INTO tmp_table(COL1,COL4) VALUES(v_date2,'2');
UPDATE tmp_table SET COL2 = (
SELECT CAST(COUNT(t.token) AS CHAR) FROM ORDER_TOKEN T, ORDER_MASTER M
  WHERE T.ORDERSID = M.ORDERSID 
  AND M.ORDER_TYPE IN ('01','23') 
  AND M.ORDER_TYPE NOT IN ('15','16')
  AND M.RES_TYPE = '02'
  AND DATE_FORMAT(M.op_time, '%Y-%m') = v_date2
) WHERE COL1 = v_date2 AND COL4 = '2';
UPDATE tmp_table SET COL3 = (
SELECT CAST(COUNT(t.token) AS CHAR) FROM ORDER_TOKEN T, ORDER_MASTER M
  WHERE T.ORDERSID = M.ORDERSID
  AND M.ORDER_TYPE NOT IN ('01','23','15','16')
  AND M.RES_TYPE = '02'
  AND DATE_FORMAT(M.op_time, '%Y-%m') = v_date2
) WHERE COL1 = v_date2 AND COL4 = '2';

SET v_date2 = DATE_FORMAT(DATE_ADD(DATE_FORMAT(CONCAT(v_date2,'-01'), '%Y-%m-%d'), INTERVAL 1 MONTH), '%Y-%m');
END WHILE;
-- ------------------------------------
SELECT COL1,COL2,COL3 FROM tmp_table;
  
END$$

DELIMITER ;

脚本2：

CALL statisticalInvestigation('2016-01');

脚本3：

DROP TABLE IF EXISTS tmp_table;
DROP PROCEDURE IF EXISTS `statisticalInvestigation`;