SELECT dc.SPEC, tg.notes, dc.TRUCK_NO, tg.SEQ_NO, tg.ID, C.MACH_NAME, tg.CREATED_ON, tg.CREATED_BY, tg.I_E_ID, tg.FCNTR, (CASE WHEN tg.notes = '验证成功' THEN tg.FCNTR ELSE dc.CNTR END) CNTR, dc.OPERA_BY, dc.TO_POS, dc.CLASS, dc.SHIPPER, dc.CNTR_OPERATOR, dc.BILL_NO, dc.MACHNO, dc.PLG_BARCODE, dc.YGC_NO, dc.PIECES, dc.DAMAGE_TYPE, dc.PT_NUM, pc.ARR_POSITION FROM fcg_yms.truck_gate tg INNER JOIN fcg_yms.C_MACHINE c ON SUBSTR( c.MACH_NAME, 3, 6 )=SUBSTR( tg.TRUCK_NO, 3, 6 ) INNER JOIN fcg_yms.dismounting_cntr dc ON dc.TRUCK_NO = c.ID AND DATE_FORMAT(tg.CREATED_ON, "%Y-%m-%d") = DATE_FORMAT(dc.CREATED_ON, "%Y-%m-%d") AND TIMESTAMPDIFF(MINUTE, tg.CREATED_ON, dc.CREATED_ON) BETWEEN 0 AND 30 LEFT JOIN fcg_yms.PORT_CNTR pc ON pc.id = dc.cntr_id WHERE tg.CREATED_ON >= '2023-05-16' AND tg.CREATED_ON <= DATE_ADD('2023-05-25', INTERVAL 1 DAY) AND tg.I_E_ID = 'I' AND dc.CREATED_ON IS NOT NULL GROUP BY tg.SEQ_NO, dc.TRUCK_NO 优化sql
最新发布