点你小JJ 点你小JJ
关注数: 6 粉丝数: 81 发帖数: 955 关注贴吧数: 6
有没有没睡觉的,会mysql的,语法错了不知道怎么改 if (txtUser='') and (startTime='') and (endTime='') then if (dataType='open') then insert into tmp_table(AlarmId,BizData,startStaus,endStatus,startDate,endDate,name) select b.AlarmId,a.BizData,b.HandleStatus,b.HandleStatus,min(b.CreateTime),min(b.CreateTime),b.Handler as name from c_alarm a join c_alarm_handler b on a.Id=b.AlarmId where b.`Handler`not in ('QE','system','AOI','PE','EV','TE') and HandleStatus='1' and b.CreateTime>curdate() group by b.AlarmId,b.HandleStatus; Create table Datas ( select b.AlarmId,b.Handler as name,b.HandleStatus,min(b.CreateTime) as CreateTime from c_alarm a join c_alarm_handler b on a.Id=b.AlarmId where b.`Handler`not in ('QE','system','AOI','PE','EV','TE') and b.HandleStatus in('1','2') and b.CreateTime>curdate() group by b.AlarmId,b.HandleStatus ); update tmp_table set name=b.name,a.startStaus=b.HandleStatus,a.endStatus=b.HandleStatus, startDate=min(b.CreateTime),endDate=max(b.CreateTime) from tmp_table a join Datas b on a.AlarmId=b.AlarmId group by b.AlarmId; END IF; if dataType='doing' then --初始化tmp_table insert into tmp_table(AlarmId,BizData,startStaus,endStatus,startDate,endDate,name) select b.AlarmId,a.BizData,b.HandleStatus,b.HandleStatus,min(b.CreateTime),min(b.CreateTime),b.Handler as name from c_alarm a join c_alarm_handler b on a.Id=b.AlarmId where b.`Handler`not in ('QE','system','AOI','PE','EV','TE') and HandleStatus='2' and b.CreateTime>curdate() group by b.AlarmId,b.HandleStatus --更新表 Create table Datas ( select b.AlarmId,b.Handler as name,b.HandleStatus,min(b.CreateTime) as CreateTime from c_alarm a join c_alarm_handler b on a.Id=b.AlarmId where b.`Handler`not in ('QE','system','AOI','PE','EV','TE') and b.HandleStatus in('2','0') and b.CreateTime>curdate() group by b.AlarmId,b.HandleStatus ); update tmp_table set name=b.name,a.startStaus=b.HandleStatus,a.endStatus=b.HandleStatus, startDate=min(b.CreateTime),endDate=max(b.CreateTime) from tmp_table a join Datas b on a.AlarmId=b.AlarmId group by b.AlarmId; END IF; if dataType='close' then --初始化tmp_table insert into tmp_table(AlarmId,BizData,startStaus,endStatus,startDate,endDate,name) select b.AlarmId,a.BizData,b.HandleStatus,b.HandleStatus,min(b.CreateTime),min(b.CreateTime),b.Handler as name from c_alarm a join c_alarm_handler b on a.Id=b.AlarmId where b.`Handler`not in ('QE','system','AOI','PE','EV','TE') and HandleStatus='0' and b.CreateTime>curdate() group by b.AlarmId,b.HandleStatus; --更新表 Create table Datas ( select b.AlarmId,b.Handler as name,b.HandleStatus,min(b.CreateTime) as CreateTime from c_alarm a join c_alarm_handler b on a.Id=b.AlarmId where b.`Handler`not in ('QE','system','AOI','PE','EV','TE') and b.HandleStatus in('0','1','2') and b.CreateTime>curdate() group by b.AlarmId,b.HandleStatus ); update tmp_table set name=b.name,a.startStaus=b.HandleStatus,a.endStatus=b.HandleStatus, startDate=min(b.CreateTime),endDate=max(b.CreateTime) from tmp_table a join Datas b on a.AlarmId=b.AlarmId group by b.AlarmId; END IF; end if;
1 下一页