|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
DROP PROCEDURE IF EXISTS order_summary_month; DELIMITER // CREATE PROCEDURE order_summary_month() BEGIN DECLARE m datetime DEFAULT '2019-01-01'; WHILE ( DATE_FORMAT(m,'%Y-%m') <= DATE_FORMAT(NOW(),'%Y-%m') ) DO IF NOT EXISTS ( SELECT insertdate FROM app.order_summary WHERE statdate=DATE_FORMAT(m,'%Y-%m') AND time_unit='3' AND type='1' ) THEN INSERT INTO app.order_summary ( statdate, time_unit, type, cnt_order, sum_price, cnt_weight, insertdate ) VALUES ( DATE_FORMAT(m,'%Y-%m'), 3, 1, ( SELECT COUNT(order_sn) AS cnt_order FROM app.wx_order WHERE FROM_UNIXTIME(order_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m') AND type='1' ), ( SELECT SUM(price) AS sum_price FROM app.wx_order WHERE FROM_UNIXTIME(finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m') AND type='1' AND gains_type='1' ), ( SELECT SUM(CAST(G.goods_weight as DECIMAL(50,2))) AS cnt_weight FROM app.wx_order O LEFT JOIN app.wx_order_goods G on O.id=G.order_id LEFT JOIN app.wx_goods_class C on G.goods_id=C.id WHERE FROM_UNIXTIME(O.finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m') AND O.type='1' ), NOW() ); -- /* --不更新旧数据则可以跳过 ELSE UPDATE app.wx_order_summary SET time_unit = 3, type = 1, cnt_order = ( SELECT COUNT(order_sn) AS cnt_order FROM app.wx_order WHERE FROM_UNIXTIME(order_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m') AND type='1' ), sum_price = ( SELECT SUM(price) AS sum_price FROM app.wx_order WHERE FROM_UNIXTIME(finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m') AND type='1' AND gains_type='1' ), cnt_weight = ( SELECT SUM(CAST(G.goods_weight as DECIMAL(50,2))) AS cnt_weight FROM app.wx_order O LEFT JOIN app.wx_order_goods G on O.id=G.order_id LEFT JOIN app.wx_goods_class C on G.goods_id=C.id WHERE FROM_UNIXTIME(O.finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m') AND O.type='1' ), insertdate = NOW() WHERE statdate = DATE_FORMAT(m,'%Y-%m') AND time_unit='3' AND type='1'; -- */ -- 不更新旧数据则可以跳过 END IF; COMMIT; SET m = DATE_SUB(m, INTERVAL -1 MONTH); END WHILE; END; // DELIMITER ; CALL order_summary_month(); |
分类： MySQL
MySQL 问题汇总
- ERROR 1146 (42S02): Table 'mysql.servers' doesn't exist
- 执行以下 SQL 语句删除并重新创建 mysql.servers 表
1234567891011121314151617USE mysqlDROP TABLE IF EXISTS servers;CREATE TABLE `servers` (`Server_name` char(64) NOT NULL DEFAULT '',`Host` char(64) NOT NULL DEFAULT '',`Db` char(64) NOT NULL DEFAULT '',`Username` char(64) NOT NULL DEFAULT '',`Password` char(64) NOT NULL DEFAULT '',`Port` int(4) NOT NULL DEFAULT '0',`Socket` char(64) NOT NULL DEFAULT '',`Wrapper` char(64) NOT NULL DEFAULT '',`Owner` char(64) NOT NULL DEFAULT '',PRIMARY KEY (`Server_name`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='MySQL Foreign Servers table';CREATE USER report01@'192.168.100.20';GRANT SELECT ON *.* TO 'report02'@'192.168.100.20' IDENTIFIED BY "Password";FLUSH PRIVILEGES;