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
时间: 2024-02-05 12:13:16 浏览: 22
这是一个比较复杂的 SQL 查询语句,可以尝试以下优化:
1. 将 WHERE 子句中的时间范围条件提取出来作为一个变量,避免在查询中多次计算:
```
SET @start_time = '2023-05-16';
SET @end_time = DATE_ADD('2023-05-25', INTERVAL 1 DAY);
```
2. 在 LEFT JOIN 中,将 ON 条件中的 cntr_id 改为 dc.ID,可以避免不必要的表连接:
```
LEFT JOIN fcg_yms.PORT_CNTR pc ON pc.ID = dc.ID
```
3. 使用 EXISTS 子查询代替 INNER JOIN:
```
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
WHERE tg.CREATED_ON >= @start_time AND tg.CREATED_ON <= @end_time AND tg.I_E_ID = 'I' AND EXISTS (
SELECT 1 FROM fcg_yms.dismounting_cntr dc2 WHERE dc2.TRUCK_NO = c.ID AND DATE_FORMAT(tg.CREATED_ON, "%Y-%m-%d") = DATE_FORMAT(dc2.CREATED_ON, "%Y-%m-%d") AND TIMESTAMPDIFF(MINUTE, tg.CREATED_ON, dc2.CREATED_ON) BETWEEN 0 AND 30
) AND dc.CREATED_ON IS NOT NULL
GROUP BY tg.SEQ_NO, dc.TRUCK_NO
```