SELECT concat( right(year(lot_info.start_date),2), date_format(lot_info.start_date,'%m') ) A5BSYM, 'L2' A5JGCD,substring( prod_lst.PROD_PN,2,3) A5KSCD,
case
when prod_lst.is_mat=1 then ''
when prod_lst.is_mat=0 then left( prod_lst.pn_desc,8) end A5TYPE ,
prod_lst.pn_desc A5ITDC,
prod_lst.prod_pn A5ITNR,'DL' A5PDWH,lot_info.lot_no A5ODNR, proc_hist.proc_id A5RSEQ,
proc_lst.proc_desc A5RSNM ,
date_format(proc_hist.out_time ,'%Y%m%d') A5EDDT,date_format(proc_hist.out_time ,'%H%i') A5EDTM,
date_format(proc_hist.in_time ,'%Y%m%d') A5STDT,date_format(proc_hist.in_time ,'%H%i') A5STTM ,
date_format(proc_hist.out_time-proc_hist.in_time ,'%H') A5LTKM,
date_format(b.out_time ,'%Y%m%d') A5zDDT,date_format(b.out_time ,'%H%i') A5zDTM,
(to_days(proc_hist.out_time)- to_days(proc_hist.in_time))*60*24+(date_format(proc_hist.out_time,'%H')-date_format(proc_hist.in_time,'%H'))*60+date_format(proc_hist.out_time,'%i')-date_format(proc_hist.in_TIME,'%i') A5LTMM,
to_days(proc_hist.out_time)- to_days(proc_hist.in_time) A5LTIM,
time_format(timediff(proc_hist.in_time,b.out_time ),'%H.%i') A5RTIM, time_format(timediff(proc_hist.in_time,b.out_time ),'%H')*60+time_format(timediff(proc_hist.in_time,b.out_time ),'%i') A5RTMM,
case when (time_format(timediff(proc_hist.in_time,b.out_time ),'%H')*60+time_format(timediff(proc_hist.in_time,b.out_time ),'%i'))<20
then '0.00-0.20'
when (time_format(timediff(proc_hist.in_time,b.out_time ),'%H')*60+time_format(timediff(proc_hist.in_time,b.out_time ),'%i'))>=20
and (time_format(timediff(proc_hist.in_time,b.out_time ),'%H')*60+time_format(timediff(proc_hist.in_time,b.out_time ),'%i'))<50
then '0.20-0.50'
when (time_format(timediff(proc_hist.in_time,b.out_time ),'%H')*60+time_format(timediff(proc_hist.in_time,b.out_time ),'%i'))>=50
and (time_format(timediff(proc_hist.in_time,b.out_time ),'%H')*60+time_format(timediff(proc_hist.in_time,b.out_time ),'%i'))<60
then '0.50-1.00'
when (time_format(timediff(proc_hist.in_time,b.out_time ),'%H')*60+time_format(timediff(proc_hist.in_time,b.out_time ),'%i'))>=60
and (time_format(timediff(proc_hist.in_time,b.out_time ),'%H')*60+time_format(timediff(proc_hist.in_time,b.out_time ),'%i'))<110
then '1.00-1.50'
when (time_format(timediff(proc_hist.in_time,b.out_time ),'%H')*60+time_format(timediff(proc_hist.in_time,b.out_time ),'%i'))>=110
and (time_format(timediff(proc_hist.in_time,b.out_time ),'%H')*60+time_format(timediff(proc_hist.in_time,b.out_time ),'%i'))<120
then '1.50-2.00'
when (time_format(timediff(proc_hist.in_time,b.out_time ),'%H')*60+time_format(timediff(proc_hist.in_time,b.out_time ),'%i'))>=120
and (time_format(timediff(proc_hist.in_time,b.out_time ),'%H')*60+time_format(timediff(proc_hist.in_time,b.out_time ),'%i'))<170
then '2.00-2.50'
when (time_format(timediff(proc_hist.in_time,b.out_time ),'%H')*60+time_format(timediff(proc_hist.in_time,b.out_time ),'%i'))>=170
and (time_format(timediff(proc_hist.in_time,b.out_time ),'%H')*60+time_format(timediff(proc_hist.in_time,b.out_time ),'%i'))<180
then '2.50-3.00'
when (time_format(timediff(proc_hist.in_time,b.out_time ),'%H')*60+time_format(timediff(proc_hist.in_time,b.out_time ),'%i'))>=180
then '3.01 以上'
end A5LTKN,'' A5BIK1,'' A5BIK2, cast(proc_hist.out_qty as UNSIGNED) A5ACPQ
FROM proc_hist
left join proc_hist b
on(
b.co_code=proc_hist.CO_CODE
and b.wip_id=proc_hist.WIP_ID
and proc_hist.WIP_SEQ-1=b.WIP_SEQ
)
left join lot_info
on(lot_info.CO_CODE=proc_hist.CO_CODE and lot_info.WIP_ID= proc_hist.WIP_ID
and proc_hist.WIP_SEQ<=lot_info.WIP_SEQ
)
left join prod_lst on (
prod_lst.co_code=lot_info.CO_CODE
and lot_info.PROD_PN=prod_lst.prod_pn )
left join proc_lst on(proc_lst.co_code=proc_hist.co_code
and proc_lst.proc_id=proc_hist.PROC_ID) where proc_hist.CO_CODE='l2300' and
lot_info.lot_no is not null and length(lot_info.lot_no)>0 and proc_hist.upd_time> date_format(date_add(now(), interval -1 day),'%Y-%m-%d 6:00')
limit 10000000