mysql 中函数如何转存_MySQL函数转储存(当前月数据同步)

1 BEGIN2 declare a1 int default 0;#第一次循环的循环变量3 declare a2 int default 0;4 declare b1 int default 0;5 declare b2 int default 0;6 declare c1 int default 0;7 declare c2 int default 0;8 declare d1 int default 0;9 declare d2 int default 0;10 declare days int default 0;#当月的第几天11 declare ids3 varchar(50);12 declare ids varchar(32);13 declare ids1 varchar(32);14 declare ids2 varchar(32);15 declare workno1 varchar(32);16 declare name1 varchar(32);17 declare amount double default 0;18 declare description1 varchar(200);19 declare description2 varchar(200);20 declare description3 varchar(200);21

22

23 #清空表xhj_sale_zmm关于昨天的数据24 DELETE FROM xhj_sale_zmm1 WHERE types_time = '03';25 #删除缓存中存在的临时表26 drop table IF EXISTS tmpTable;27 drop table IF EXISTS ordermain1;28 drop table IF EXISTS ordermain2;29

30 # 创建临时表– 不存在则创建临时表31 create temporary table ifnot exists tmpTable32 (33 #id varchar(50),34 #id bigint(32) unsigned NOT NULL auto_increment primary key,# 主键id35 ranking int,# 排名36 names1 varchar(32),# 销售员姓名37 number varchar(32),# 工号38 dept varchar(500),# 部门描述39 deptid varchar(50),# 部门id40 amounts double(32,0),# 金额41 types_dept varchar(32),# 部门类别42 type_time varchar(50)# 时间类别43 );44 # 使用前先清空临时表。45 truncate TABLE tmpTable;46 #创建临时表ordermain1并47 create temporary table ordermain1 SELECT id,description FROM t_zmm2 where (parentdepartid is null or parentdepartid = '') and status = '0';48

49 create temporary table ordermain2 (SELECT @rownum:=@rownum+1 AS rownum1, ordermain1.* FROM (SELECT @rownum:=0) r, ordermain1);50 #查询临时表的总行数51 select max(o.rownum1) into a1 from ordermain2 o;52 set a2 = 1;53 while(a2 <=a1) DO54 SELECT id,description into ids,description1 from ordermain2 where rownum1 =a2;55 if ids is not null and ids != ''then56 drop table IF EXISTS table1;57 drop table IF EXISTS table11;58 drop table IF EXISTS table2;59 drop table IF EXISTS table3;60 #创建临时表单161 SELECT *into days from ((SELECT DAYOFMONTH(NOW())) as b);#查询当前是本月第几天；62 #判断是否为当月第一天63 if days = 1then64 create temporary table table1 SELECT u.realname,a.workno,sum((m.performance_amount * a.scale * 0.01)) as a,'03' as day,'01'as bumen FROM65 xhj_sale_zmm3 a LEFT JOIN xhj_sale_zmm1 m on a.oid=m.id LEFT JOIN t_zmm4 u on a.workno = u.username LEFT JOIN t_s_zmm5 o on u.id = o.user_id LEFT JOIN t_zmm2 d on o.org_id =d.id66 where d.depart_order like CONCAT('%',ids,'%') and m.confirm_time between (select CONCAT( DATE_FORMAT((SELECT DATE_ADD(NOW(),INTERVAL -1 MONTH)), "%Y-%m"),'-01 00:00:00')) and (select CONCAT(DATE_SUB(curdate(),INTERVAL 1 DAY),' 23:59:59')) and67 m.order_time between (select CONCAT( DATE_FORMAT((SELECT DATE_ADD(NOW(),INTERVAL -1 MONTH)), "%Y-%m"),'-01 00:00:00')) and (select CONCAT(DATE_SUB(curdate(),INTERVAL 0 DAY),' 23:59:59')) and m.status not in ('1','3') group by a.workno order by a DESC limit 10;68 #添加名次69 create temporary table table11 (SELECT @rownum:=@rownum+1 AS rownum11, table1.* FROM (SELECT @rownum:=0) r, table1);70 #把得到的数据插入到临时表单中71 #SELECT *,a2 as aa FROM table11;72 INSERT INTO tmpTable(ranking,names1,number,dept,deptid,amounts,types_dept,type_time)73 SELECT rownum11,realname,workno,description1,ids,a,bumen,day FROM table11;74 end if;75 if days != 1then76 create temporary table table1 SELECT u.realname,a.workno,sum((m.performance_amount * a.scale * 0.01)) as a,'03' as day,'01'as bumen FROM77 xhj_sale_zmm3 a LEFT JOIN xhj_sale_zmm1 m on a.oid=m.id LEFT JOIN t_zmm4 u on a.workno = u.username LEFT JOIN t_s_zmm5 o on u.id = o.user_id LEFT JOIN t_zmm2 d on o.org_id =d.id78 where d.depart_order like CONCAT('%',ids,'%') and m.confirm_time between (select CONCAT( DATE_FORMAT(now(), "%Y-%m"),'-01 00:00:00')) and (select CONCAT(DATE_SUB(curdate(),INTERVAL 1 DAY),' 23:59:59')) and79 m.order_time between (select CONCAT( DATE_FORMAT(now(), "%Y-%m"),'-01 00:00:00')) and (select CONCAT(DATE_SUB(curdate(),INTERVAL 0 DAY),' 23:59:59')) and m.status not in ('1','3') group by a.workno order by a DESC limit 10;80 #添加名次81 create temporary table table11 (SELECT @rownum:=@rownum+1 AS rownum11, table1.* FROM (SELECT @rownum:=0) r, table1);82 #把得到的数据插入到临时表单中83 #SELECT *,a2 as aa FROM table11;84 INSERT INTO tmpTable(ranking,names1,number,dept,deptid,amounts,types_dept,type_time)85 SELECT rownum11,realname,workno,description1,ids,a,bumen,day FROM table11;86 end if;87 #创建临时表table2---获取二级事业部门id88 create temporary table table2 SELECT id,description FROM t_zmm2 where LENGTH(depart_order) = 64 and fax is not null and fax != '' and status = '0' and depart_order like CONCAT('%',ids,'%');89 create temporary table table3 (SELECT @rownum:=@rownum+1 AS rownum, table2.* FROM (SELECT @rownum:=0) r, table2);90 select max(t.rownum) into b1 from table3 t;91 set b2 = 1;92 while(b2 <=b1) DO93 drop table IF EXISTS table21;94 drop table IF EXISTS table4;95 drop table IF EXISTS table5;96 drop table IF EXISTS table7;97 SELECT id,description into ids1,description2 from table3 where rownum =b2;98 #SELECT ids1;99 if days = 1then100 create temporary table table7 SELECT u.realname,a.workno,sum((m.performance_amount * a.scale * 0.01)) as a,'03' as day,'02'as bumen FROM101 xhj_sale_zmm3 a LEFT JOIN xhj_sale_zmm1 m on a.oid=m.id LEFT JOIN t_zmm4 u on a.workno = u.username LEFT JOIN t_zmm5 o on u.id = o.user_id LEFT JOIN t_zmm2 d on o.org_id =d.id102 where d.depart_order like CONCAT('%',ids1,'%') and m.confirm_time between (select CONCAT( DATE_FORMAT(now(), "%Y-%m"),'-01 00:00:00')) and (select CONCAT(DATE_SUB(curdate(),INTERVAL 1 DAY),' 23:59:59')) and103 m.order_time between (select CONCAT( DATE_FORMAT((SELECT DATE_ADD(NOW(),INTERVAL -1 MONTH)), "%Y-%m"),'-01 00:00:00')) and (select CONCAT(DATE_SUB(curdate(),INTERVAL 0 DAY),' 23:59:59')) and m.status not in ('1','3') group by a.workno order by a DESC limit 5;104 #添加名次105 create temporary table table21 (SELECT @rownum:=@rownum+1 AS rownum21, table7.* FROM (SELECT @rownum:=0) r, table7);106 #把得到的数据插入到临时表单中107 INSERT INTO tmpTable(ranking,names1,number,dept,deptid,amounts,types_dept,type_time)108 SELECT rownum21,realname,workno,description2,ids1,a,bumen,day FROM table21;109 end if;110 if days != 1then111 create temporary table table7 SELECT u.realname,a.workno,sum((m.performance_amount * a.scale * 0.01)) as a,'03' as day,'02'as bumen FROM112 xhj_sale_zmm3 a LEFT JOIN xhj_sale_zmm1 m on a.oid=m.id LEFT JOIN t_zmm4 u on a.workno = u.username LEFT JOIN t_zmm5 o on u.id = o.user_id LEFT JOIN t_zmm2 d on o.org_id =d.id113 where d.depart_order like CONCAT('%',ids1,'%') and m.confirm_time between (select CONCAT( DATE_FORMAT(now(), "%Y-%m"),'-01 00:00:00')) and (select CONCAT(DATE_SUB(curdate(),INTERVAL 1 DAY),' 23:59:59')) and114 m.order_time between (select CONCAT( DATE_FORMAT(now(), "%Y-%m"),'-01 00:00:00')) and (select CONCAT(DATE_SUB(curdate(),INTERVAL 0 DAY),' 23:59:59')) and m.status not in ('1','3') group by a.workno order by a DESC limit 5;115 #添加名次116 create temporary table table21 (SELECT @rownum:=@rownum+1 AS rownum21, table7.* FROM (SELECT @rownum:=0) r, table7);117 #把得到的数据插入到临时表单中118 INSERT INTO tmpTable(ranking,names1,number,dept,deptid,amounts,types_dept,type_time)119 SELECT rownum21,realname,workno,description2,ids1,a,bumen,day FROM table21;120 end if;121 #创建临时表table5---获取三级部门id122 create temporary table table4 SELECT id,description FROM t_zmm2 where LENGTH(depart_order) = 96 and status = '0' and depart_order like CONCAT('%',ids1,'%');123 create temporary table table5 (SELECT @rownum:=@rownum+1 AS rownum3, table4.* FROM (SELECT @rownum:=0) r, table4);124 select max(t2.rownum3) into c1 from table5 t2;125 set c2 = 1;126 while(c2 <=c1) DO127 drop table IF EXISTS table31;128 drop table IF EXISTS table6;129 #得到三级部门前三名信息130 if days = 1then131 SELECT id,description into ids2,description3 from table5 where rownum3 =c2;132 create temporary table table6 SELECT u.realname,a.workno,sum((m.performance_amount * a.scale * 0.01)) as a,'03' as day,'03'as bumen FROM133 xhj_sale_zmm3 a LEFT JOIN xhj_sale_zmm1 m on a.oid=m.id LEFT JOIN t_zmm4 u on a.workno = u.username LEFT JOIN t_zmm5 o on u.id = o.user_id LEFT JOIN t_zmm2 d on o.org_id =d.id134 where d.depart_order like CONCAT('%',ids2,'%') and m.confirm_time between (select CONCAT( DATE_FORMAT(now(), "%Y-%m"),'-01 00:00:00')) and (select CONCAT(DATE_SUB(curdate(),INTERVAL 1 DAY),' 23:59:59')) and135 m.order_time between (select CONCAT( DATE_FORMAT((SELECT DATE_ADD(NOW(),INTERVAL -1 MONTH)), "%Y-%m"),'-01 00:00:00')) and (select CONCAT(DATE_SUB(curdate(),INTERVAL 0 DAY),' 23:59:59')) and m.status not in ('1','3') group by a.workno order by a DESC limit 3;136 #添加名次137 create temporary table table31 (SELECT @rownum:=@rownum+1 AS rownum31, table6.* FROM (SELECT @rownum:=0) r, table6);138 #把得到的数据插入到临时表单中139 INSERT INTO tmpTable(ranking,names1,number,dept,deptid,amounts,types_dept,type_time)140 SELECT rownum31,realname,workno,description3,ids2,a,bumen,day FROM table31;141

142 end if;143 if days != 1then144 SELECT id,description into ids2,description3 from table5 where rownum3 =c2;145 create temporary table table6 SELECT u.realname,a.workno,sum((m.performance_amount * a.scale * 0.01)) as a,'03' as day,'03'as bumen FROM146 xhj_sale_zmm3 a LEFT JOIN xhj_sale_zmm1 m on a.oid=m.id LEFT JOIN t_zmm4 u on a.workno = u.username LEFT JOIN t_zmm5 o on u.id = o.user_id LEFT JOIN t_zmm2 d on o.org_id =d.id147 where d.depart_order like CONCAT('%',ids2,'%') and m.confirm_time between (select CONCAT( DATE_FORMAT(now(), "%Y-%m"),'-01 00:00:00')) and (select CONCAT(DATE_SUB(curdate(),INTERVAL 1 DAY),' 23:59:59')) and148 m.order_time between (select CONCAT( DATE_FORMAT(now(), "%Y-%m"),'-01 00:00:00')) and (select CONCAT(DATE_SUB(curdate(),INTERVAL 0 DAY),' 23:59:59')) and m.status not in ('1','3') group by a.workno order by a DESC limit 3;149 #添加名次150 create temporary table table31 (SELECT @rownum:=@rownum+1 AS rownum31, table6.* FROM (SELECT @rownum:=0) r, table6);151 #把得到的数据插入到临时表单中152 INSERT INTO tmpTable(ranking,names1,number,dept,deptid,amounts,types_dept,type_time)153 SELECT rownum31,realname,workno,description3,ids2,a,bumen,day FROM table31;154 end if;155 drop table IF EXISTS table31;156 drop table IF EXISTS table6;157 set c2 = c2 + 1;158 end while;159 drop table IF EXISTS table21;160 drop table IF EXISTS table4;161 drop table IF EXISTS table5;162 drop table IF EXISTS table7;163 set b2 = b2 + 1;164 end while;165 drop table IF EXISTS table1;166 drop table IF EXISTS table11;167 drop table IF EXISTS table2;168 drop table IF EXISTS table3;169 end if;170 set a2 = a2 + 1;171 end while;172

173 #删除缓存中存在的临时表174

175 INSERT INTO xhj_sale_zmm(ranking,name,number,dept,deptid,amount,types_dept,types_time)176 SELECT ranking,names1,number,dept,deptid,amounts,types_dept,type_time FROM tmpTable;177 SELECT *FROM xhj_sale_zmm;178 drop table IF EXISTS ordermain1;179 drop table IF EXISTS ordermain2;180 drop table IF EXISTS tmpTable;181 END