SQL语句

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

转载于:https://www.cnblogs.com/greefsong/archive/2013/06/05/3119103.html