mysql自动计算脚本_mysql一次统计脚本

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 EXISTStmp_table;CREATE TEMPORARY TABLEtmp_table (

COL1VARCHAR(35),

COL2VARCHAR(35),

COL3VARCHAR(35),

COL4VARCHAR(1)

);INSERT INTOtmp_table(COL1, COL2, COL3)SELECT 'Electricity Meter quantity',CAST(COUNT(1) AS CHAR),'' FROMDA_BJ;INSERT INTOtmp_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 INTOtmp_table(COL1, COL2, COL3)VALUES('','','');INSERT INTOtmp_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') DOINSERT INTO tmp_table(COL1,COL4) VALUES(v_date,'1');UPDATE tmp_table SET COL2 =(SELECT CAST(COUNT(t.token) AS CHAR) FROMORDER_TOKEN T, ORDER_MASTER MWHERE T.ORDERSID =M.ORDERSIDAND 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) FROMORDER_TOKEN T, ORDER_MASTER MWHERE T.ORDERSID =M.ORDERSIDAND 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 INTOtmp_table(COL1, COL2, COL3)VALUES('','','');INSERT INTOtmp_table(COL1, COL2, COL3)SELECT 'Water Meter quantity',CAST(COUNT(1) AS CHAR),'' FROMDA_SB;INSERT INTOtmp_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 INTOtmp_table(COL1, COL2, COL3)VALUES('','','');INSERT INTOtmp_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') DOINSERT INTO tmp_table(COL1,COL4) VALUES(v_date2,'2');UPDATE tmp_table SET COL2 =(SELECT CAST(COUNT(t.token) AS CHAR) FROMORDER_TOKEN T, ORDER_MASTER MWHERE T.ORDERSID =M.ORDERSIDAND 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) FROMORDER_TOKEN T, ORDER_MASTER MWHERE T.ORDERSID =M.ORDERSIDAND 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 FROMtmp_table;END$$

DELIMITER ;