1 BEGIN 2 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 if not exists tmpTable 32 ( 33 #id varchar(50), 34 #id bigint(32) unsigned NOT NULL auto_increment primary key,# 主键id 35 ranking int,# 排名 36 names1 varchar(32),# 销售员姓名 37 number varchar(32),# 工号 38 dept varchar(500),# 部门描述 39 deptid varchar(50),# 部门id 40 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) DO 54 SELECT id,description into ids,description1 from ordermain2 where rownum1 = a2; 55 if ids is not null and ids != '' then 56 drop table IF EXISTS table1; 57 drop table IF EXISTS table11; 58 drop table IF EXISTS table2; 59 drop table IF EXISTS table3; 60 #创建临时表单1 61 SELECT * into days from ((SELECT DAYOFMONTH(NOW())) as b);#查询当前是本月第几天； 62 #判断是否为当月第一天 63 if days = 1 then 64 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 FROM 65 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.id 66 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')) and 67 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 != 1 then 76 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 FROM 77 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.id 78 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')) and 79 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---获取二级事业部门id 88 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) DO 93 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 = 1 then 100 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 FROM 101 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.id 102 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')) and 103 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 != 1 then 111 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 FROM 112 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.id 113 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')) and 114 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---获取三级部门id 122 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) DO 127 drop table IF EXISTS table31; 128 drop table IF EXISTS table6; 129 #得到三级部门前三名信息 130 if days = 1 then 131 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 FROM 133 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.id 134 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')) and 135 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 != 1 then 144 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 FROM 146 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.id 147 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')) and 148 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