CREATE DEFINER=`root`@`%` PROCEDURE `getReportWeavingProductionDay`(in startTime datetime,in endTime datetime,in factoryIds varchar(100),in machineIds varchar(5000),in PageSize int,in DataFromIndex int) BEGIN -- 参数说明 -- startTime:开始时间,endTime:结束时间,factoryIds:工厂ID,多个工厂用逗号隔开,machineIds:机器ID,多个机器ID用逗号隔开 -- 1、判断结果临时表是否存在 drop temporary table if exists ReportWeavingProductionDay; -- 要确认会不会删除其他库 -- 2、数据计算 -- 2.1、参数处理 -- 2.2、分页计算 -- set @DataFromIndex=PageIndex * PageSize; -- 2.3 、锁定主表范围,填充数据(plan_id正常>0即可,让走索引,用>=1,select部分略快,1000是永远小于plan_id,所以没问题) create temporary table ReportWeavingProductionDay as select A.* from terminal_output_data A left join base_machine_loom B on A.machine_id = B.machine_id where A.plan_id>=10000 and B.status='3' and B.is_forbidden='0' and A.work_date>=startTime and A.work_date<=endTime and case factoryIds when '' then 1=1 else find_in_set(B.factory_id,factoryIds) end and case machineIds when '' then 1=1 else find_in_set(B.machine_loom_id,machineIds) end limit PageSize offset DataFromIndex; alter table ReportWeavingProductionDay add index idx_ReportWeavingProductionDay_workrecordid(work_record_id); alter table ReportWeavingProductionDay add index idx_ReportWeavingProductionDay_planid(plan_id); alter table ReportWeavingProductionDay add index idx_ReportWeavingProductionDay_machineid(machine_id); -- 3.1 大表数据取锁定范围,写入临时表 -- tmp_work_order_production_plan drop temporary table if exists tmp_work_order_production_plan; create temporary table tmp_work_order_production_plan as select * from work_order_production_plan where only_no in ( select distinct plan_id from ReportWeavingProductionDay ); alter table tmp_work_order_production_plan add index idx_tmpworkorder_planid(only_no); set sql_safe_updates=false; alter table ReportWeavingProductionDay add IsShoe_Name varchar(20); update ReportWeavingProductionDay A left join tmp_work_order_production_plan B on A.plan_id=B.only_no and B.size_number<>'' and B.size_number is not null set A.IsShoe_Name='鞋型'; update ReportWeavingProductionDAy A left join tmp_work_order_production_plan B on A.plan_id=B.only_no and (B.size_number='' or B.size_number is null) set A.IsShoe_Name='公版'; set sql_safe_updates=true; -- tmp_tmp_speed drop temporary table if exists tmp_tmp_speed; create temporary table tmp_tmp_speed as select work_record_id,speed_value from tmp_speed where work_record_id in ( select distinct work_record_id from ReportWeavingProductionDay ); alter table tmp_tmp_speed add index idx_tempspeed_workrecordid(work_record_id); -- tmp_base_machine_loom drop temporary table if exists tmp_base_machine_loom; create temporary table tmp_base_machine_loom as select machine_id,machine_type_id,factory_id,machine_name,unit_group,collection_mode from base_machine_loom; alter table tmp_base_machine_loom add index idx_machineloom_machineid(machine_id); -- 3.2 计算需要关联的列 -- base_machine_loom表相关 alter table ReportWeavingProductionDay add machine_type_id bigint ,add machine_name varchar(50) ,add factory_id bigint ,add unit_group varchar(50) ,add collection_mode varchar(10); set sql_safe_updates=false; update ReportWeavingProductionDay A left join tmp_base_machine_loom B on a.machine_id=b.machine_id set A.machine_type_id=B.machine_type_id ,A.machine_name=B.machine_name ,A.factory_id=B.factory_id ,A.unit_group=B.unit_group ,A.collection_mode=B.collection_mode; set sql_safe_updates=true; -- base_machine_type表相关,machine_type_name(机器类型) alter table ReportWeavingProductionDay add machine_type_name varchar(50); set sql_safe_updates=false; update ReportWeavingProductionDay A left join base_machine_type B on A.machine_type_id=B.machine_type_id set A.machine_type_name=B.machine_type_name; set sql_safe_updates=true; -- base_staff表相关, alter table ReportWeavingProductionDay add staff_username varchar(50); set sql_safe_updates=false; update ReportWeavingProductionDay A left join base_staff B on A.work_man_id=B.staff_id set A.staff_username=B.staff_username; set sql_safe_updates=true; -- base_work_class表相关 alter table ReportWeavingProductionDay add work_class_name varchar(50); set sql_safe_updates=false; update ReportWeavingProductionDay A left join base_work_class B on A.work_class_id=B.work_class_id set A.work_class_name=B.work_class_name; set sql_safe_updates=true; -- work_order_production_plan表相关 alter table ReportWeavingProductionDay add order_type varchar(50) ,add material_name varchar(255) ,add sale_order_no varchar(100) ,add color_name varchar(255) ,add muslin_weft_density decimal(10,2) ,add machine_density decimal(10,2) ,add row_card varchar(255) ,add size_name varchar(255) ,add pair_count decimal(10,2) -- 双数 ,add yards_count decimal(10,2); -- 码数 -- work_order_production_plan 表相关 set sql_safe_updates=false; update ReportWeavingProductionDay A left join tmp_work_order_production_plan B on A.plan_id=B.only_no set A.order_type=B.order_type ,A.material_name=B.material_name ,A.sale_order_no=B.sale_order_no ,A.color_name=B.color_name ,A.muslin_weft_density=B.muslin_weft_density ,A.machine_density=B.machine_density ,A.row_card=B.row_card ,A.size_name=B.size_name; set sql_safe_updates=true; -- speed_value 表相关 alter table ReportWeavingProductionDay add speed_value decimal(10,2); set sql_safe_updates=false; update ReportWeavingProductionDay A left join tmp_tmp_speed B on A.work_record_id=B.work_record_id set A.speed_value=B.speed_value; set sql_safe_updates=true; -- 上轴时长、穿纱时长、机修时长 alter table ReportWeavingProductionDay add times_shagnzhou decimal(10,2) ,add times_chuansha decimal(10,2) ,add times_jixiu decimal(10,2); -- terminal_action_class_itemb表相关 drop temporary table if exists tmp_terminal_action_class_item; create temporary table tmp_terminal_action_class_item as select work_record_id,type_id,begin_time,end_time ,round(sum(timestampdiff(second, begin_time, end_time) / 60), 1) as Sum_TimeLong from terminal_action_class_item where work_record_id in ( select distinct work_record_id from ReportWeavingProductionDay ) group by work_record_id,type_id,begin_time,end_time; alter table tmp_terminal_action_class_item add index idex_actionclassitem(work_record_id); set sql_safe_updates=false; -- 上轴时长 update ReportWeavingProductionDay A left join tmp_terminal_action_class_item B on A.work_record_id=B.work_record_id and B.type_id=1 set A.times_shagnzhou=Sum_TimeLong; -- 穿纱时长 update ReportWeavingProductionDay A left join tmp_terminal_action_class_item B on A.work_record_id=B.work_record_id and B.type_id=2 set A.times_chuansha=Sum_TimeLong; -- 机修时长 update ReportWeavingProductionDay A left join tmp_terminal_action_class_item B on A.work_record_id=B.work_record_id and B.type_id=3 set A.times_jixiu=Sum_TimeLong; set sql_safe_updates=true; -- 经停时长(statusid=3)、纬停时长(statusid=1)、断筒纱时长(statusid=4) alter table ReportWeavingProductionDay add times_jtsc decimal(10,2) ,add times_wtsc decimal(10,2) ,add times_dtssc decimal(10,2); -- 经停时长(times_jtsc)、纬停时长(times_wtsc)、断筒纱时长(times_dtssc) drop temporary table if exists tmp_terminal_machine_status_detail; create temporary table tmp_terminal_machine_status_detail as select MachineID,SpecRecordID,statusid,statusname ,round(sum(StopLong)/60,1) as Sum_StopLong from terminal_machine_status_detail where SpecRecordID in ( select distinct spec_record_id from ReportWeavingProductionDay ) group by MachineID,SpecRecordID,statusid,statusname; alter table tmp_terminal_machine_status_detail add index idx_machinestatus_specrecordid(SpecRecordID); set sql_safe_updates=false; -- 经停时长(times_jtsc)字段更新 update ReportWeavingProductionDay A left join tmp_terminal_machine_status_detail B on A.machine_id=B.MachineID and A.spec_record_id=B.SpecRecordID and B.statusid=3 set A.times_jtsc=Sum_StopLong; -- 纬停时长(times_wtsc)字段更新 update ReportWeavingProductionDay A left join tmp_terminal_machine_status_detail B on A.machine_id=B.MachineID and A.spec_record_id=B.SpecRecordID and B.statusid=1 set A.times_wtsc=Sum_StopLong; -- 断筒纱时长(times_dtssc)字段更新 update ReportWeavingProductionDay A left join tmp_terminal_machine_status_detail B on A.machine_id=B.MachineID and A.spec_record_id=B.SpecRecordID and B.statusid=4 set A.times_dtssc=Sum_StopLong; set sql_safe_updates=true; -- 异常双数、异常码数 alter table ReportWeavingProductionDay add qty_ycss decimal(10,2) ,add qty_ycms decimal(10,2); drop temporary table if exists tmp_bad_class_detail; create temporary table tmp_bad_class_detail as select WorkRecordID ,case when BadLeft>BadRight then BadLeft else BadRight end as qty_ycss ,sum(CodeNum) as qty_ycms from bad_class_detail where WorkRecordID in ( select distinct work_record_id from ReportWeavingProductionDay )group by WorkRecordID,BadLeft,BadRight; set sql_safe_updates=false; update ReportWeavingProductionDay A left join tmp_bad_class_detail B on A.work_record_id=B.WorkRecordID set A.qty_ycss=B.qty_ycss ,A.qty_ycms=B.qty_ycms; set sql_safe_updates=true; -- 其他停机 -- 保养中 set sql_safe_updates=false; alter table ReportWeavingProductionDay add other_stop_baoyangzhong int; update ReportWeavingProductionDay A left join ( select timestampdiff(second,stop_begin_time,stop_end_time) as total_time ,A.work_record_id from terminal_other_stop_detail A left join base_halt_reason_type B on A.terminal_other_stop_detail_id=B.halt_reason_type_id where B.halt_type_name='保养中' ) B on A.work_record_id=B.work_record_id set A.other_stop_baoyangzhong=B.total_time; -- 赋值字段 set sql_safe_updates=true; -- 待单中 set sql_safe_updates=false; alter table ReportWeavingProductionDay add other_stop_daidanzhong int; update ReportWeavingProductionDay A left join ( select timestampdiff(second,stop_begin_time,stop_end_time) as total_time ,A.work_record_id from terminal_other_stop_detail A left join base_halt_reason_type B on A.terminal_other_stop_detail_id=B.halt_reason_type_id where B.halt_type_name='待单中' -- 其他停机类型 ) B on A.work_record_id=B.work_record_id set A.other_stop_daidanzhong=B.total_time; -- 赋值字段 set sql_safe_updates=true; -- 待花型 set sql_safe_updates=false; alter table ReportWeavingProductionDay add other_stop_daihuaxing int; update ReportWeavingProductionDay A left join ( select timestampdiff(second,stop_begin_time,stop_end_time) as total_time ,A.work_record_id from terminal_other_stop_detail A left join base_halt_reason_type B on A.terminal_other_stop_detail_id=B.halt_reason_type_id where B.halt_type_name='待花型' -- 其他停机类型 ) B on A.work_record_id=B.work_record_id set A.other_stop_daihuaxing=B.total_time; -- 赋值字段 set sql_safe_updates=true; -- 待修中 set sql_safe_updates=false; alter table ReportWeavingProductionDay add other_stop_daixiuzhong int; update ReportWeavingProductionDay A left join ( select timestampdiff(second,stop_begin_time,stop_end_time) as total_time ,A.work_record_id from terminal_other_stop_detail A left join base_halt_reason_type B on A.terminal_other_stop_detail_id=B.halt_reason_type_id where B.halt_type_name='待修中' -- 其他停机类型 ) B on A.work_record_id=B.work_record_id set A.other_stop_daixiuzhong=B.total_time; -- 赋值字段 set sql_safe_updates=true; -- 待上轴 set sql_safe_updates=false; alter table ReportWeavingProductionDay add other_stop_daishangzhou int; update ReportWeavingProductionDay A left join ( select timestampdiff(second,stop_begin_time,stop_end_time) as total_time ,A.work_record_id from terminal_other_stop_detail A left join base_halt_reason_type B on A.terminal_other_stop_detail_id=B.halt_reason_type_id where B.halt_type_name='待上轴' -- 其他停机类型 ) B on A.work_record_id=B.work_record_id set A.other_stop_daishangzhou=B.total_time; -- 赋值字段 set sql_safe_updates=true; -- 待穿纱 set sql_safe_updates=false; alter table ReportWeavingProductionDay add other_stop_daichuansha int; update ReportWeavingProductionDay A left join ( select timestampdiff(second,stop_begin_time,stop_end_time) as total_time ,A.work_record_id from terminal_other_stop_detail A left join base_halt_reason_type B on A.terminal_other_stop_detail_id=B.halt_reason_type_id where B.halt_type_name='待穿纱' -- 其他停机类型 ) B on A.work_record_id=B.work_record_id set A.other_stop_daichuansha=B.total_time; -- 赋值字段 set sql_safe_updates=true; -- 4、查询结果 -- 总条数 -- select count(0) into TatalCount from ReportWeavingProductionDay; -- select * from ReportWeavingProductionDay where order_type is not null; select ifnull(machine_id,'') as '机器ID' ,ifnull(GROUP_CONCAT(work_record_id SEPARATOR ','),'') as '工作记录ID' ,DATE_FORMAT(work_date,'%Y-%m-%d') as '日期' ,ifnull(order_type,'') as '订单类型' ,ifnull(machine_type_name,'') as '设备类型' ,ifnull(machine_name,'') as '机台号' ,ifnull(unit_group,'') as '组别' ,ifnull(work_class_name,'') as '班别' ,ifnull(staff_username,'') as '员工姓名' ,ifnull(material_name,'') as '品名' ,case IsShoe_Name when '' then '' when null then '' else '鞋型' end as '是否鞋型' ,ifnull(sale_order_no,'') as '订单编号' ,ifnull(color_name,'') as '颜色' ,ifnull(muslin_weft_density,'') as '胚布纬密' ,ifnull(machine_density,'') as '上机纬密' ,ifnull(max(speed_value),'') as '机速' ,ifnull(row_card,'') as '预设排数' ,ifnull(size_name,'') as '尺码' ,ifnull(case when size_name is not null and size_name != '' then sum(order_nums) else null end,'') as '双数' ,ifnull(case when size_name is null or size_name = '' then sum(order_nums) else null end,'') as '码数' ,work_nums as '生产米数' ,count as '生产梭数' ,ifnull(round(sum(timestampdiff(second, work_begin_time, work_end_time) / 60), 1),0) as '工作时间' ,ifnull(round(sum(stop_times)/60,1),0) as '总停机时间' ,ifnull(times_shagnzhou,'') as '上轴时长' ,ifnull(times_chuansha,'') as '穿纱时长' ,ifnull(times_jixiu,'') as '机修时长' ,ifnull(times_jtsc,'') as '经停时长' ,ifnull(times_wtsc,'') as '纬停时长' ,ifnull(times_dtssc,'') as '断筒纱时长' ,ifnull(pair_count,'') as '异常双数' ,ifnull(yards_count,'') as '异常码数' ,(ifnull(round(sum(timestampdiff(second, work_begin_time, work_end_time) / 60), 1),0)-(ifnull(times_shagnzhou,0)+ifnull(times_chuansha,0)+ifnull(times_jixiu,0))) as '实际作业时间' ,CONCAT(ifnull(round(((ifnull(round(sum(timestampdiff(second, work_begin_time, work_end_time) / 60), 1),0)-ifnull(round(sum(stop_times)/60,1),0))/ifnull(round(sum(timestampdiff(second, work_begin_time, work_end_time) / 60), 1),0)),2)*100,0),'%') as '机台效率' ,round((ifnull(round(sum(timestampdiff(second, work_begin_time, work_end_time) / 60), 1),0)-ifnull(round(sum(stop_times)/60,1),0)),2) as '设备运转时间' ,(ifnull(round(sum(stop_times)/60,1),0)-ifnull(times_jtsc,0)-ifnull(times_wtsc,0)-ifnull(times_dtssc,0)) as '其他停机' ,ifnull(sum(other_stop_baoyangzhong),0) as '保养中' ,ifnull(sum(other_stop_daidanzhong),0) as '待单中' ,ifnull(sum(other_stop_daihuaxing),0) as '待花型' ,ifnull(sum(other_stop_daixiuzhong),0) as '待修中' ,ifnull(sum(other_stop_daishangzhou),0) as '待上轴' ,ifnull(sum(other_stop_daichuansha),0) as '待穿纱' from ReportWeavingProductionDay group by machine_id,work_date,order_type,machine_type_name,machine_type_name,machine_name,unit_group,work_class_name,staff_username,material_name,sale_order_no,color_name ,muslin_weft_density,machine_density,row_card,size_name,work_nums,count ,times_shagnzhou,times_chuansha,times_jixiu ,pair_count,yards_count ,times_jtsc,times_wtsc,times_dtssc ,IsShoe_Name ; END
标签:存储,set,示例,--,work,machine,ifnull,MySQL,id From: https://www.cnblogs.com/chengeng/p/18370918