脚本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`;