萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL 多表聯查實戰:四種寫法效能對比

場景說明

需關聯 order_info、ivrlog4ivrdlvinst、delivery_info 三張表,依條件篩選並取得最新記錄。

方法一:隱式內連接 + 條件過濾

SELECT * FROM order_info AS a, ivrlog4ivrdlvinst AS b
WHERE a.saleorder = b.ext1_skill
  AND b.start_date = @date1
  AND se_id = '55'
  AND b.ext1_skill != ''
  AND CONVERT(varchar(10), a.instime, 112) = @date2
  AND MAX(a.instime);  -- 此處語法有誤,MAX 不能直接用於 WHERE

方法二:子查詢 + 外層過濾

SELECT * FROM order_info AS a
WHERE a.saleorder = (
    SELECT b.ext1_skill FROM ivrlog4ivrdlvinst AS b
    WHERE b.start_date = @date1 AND se_id = '55' AND b.ext1_skill != ''
)
AND CONVERT(varchar(10), MAX(a.instime), 112) = @date2;  -- 同樣語法錯誤

方法三:變數預設 + 子查詢

DECLARE @date1 VARCHAR(20), @date2 VARCHAR(20);
SET @date1 = '20100812'; SET @date2 = '2010-08-12';
SELECT * FROM order_info AS a
WHERE a.saleorder = (
    SELECT b.ext1_skill FROM ivrlog4ivrdlvinst AS b
    WHERE b.start_date = @date1 AND se_id = '55' AND b.ext1_skill != ''
)
AND CONVERT(varchar(10), a.instime, 112) = @date2
AND MAX(a.instime);  -- 仍有語法問題

方法四:明確多表 JOIN + 子查詢篩選

SELECT b.caller, b.start_date, b.start_time, b.ext1_skill,
       c.deliveryno, c.destroyresult, c.deliverydate, c.deliverytime,
       c.arrangetime, c.driverphone, c.drivermobile,
       a.servicedate, a.servicetime, a.workertel
FROM order_info AS a
JOIN ivrlog4ivrdlvinst AS b ON a.saleorder = b.ext1_skill
JOIN delivery_info AS c ON a.saleorder = c.orderid  -- 假設關聯鍵
WHERE b.start_date = @date1
  AND b.se_id = '55'
  AND b.ext1_skill != ''
  AND CONVERT(varchar(10), a.instime, 112) = @date2
ORDER BY b.start_date DESC, b.start_time DESC;

效能建議

  • 優先使用明確的 JOIN 語法,避免隱式連接。
  • 子查詢若返回單值可用 =,多值用 IN 或 EXISTS。
  • 避免在 WHERE 中直接使用聚合函數(如 MAX),應改用子查詢或視窗函數。
  • 確保關聯欄位有索引,且資料型別一致以避免隱含轉換。
copyright © 萬盛學電腦網 all rights reserved