在 MySQL 中使用聚合函數連接表時如何避免 SUM 值相乘？

避免 MySQL 中使用聚合函數連接表時 SUM 值相乘的技巧

在 MySQL 查詢中組合多個連接操作時，如果使用 SUM 等聚合函數，可能會導致意外結果。這是因為連接表的行笛卡爾積會使求和結果相乘，導致結果不準確。

假設存在一個例子，最初使用兩個查詢從不同的表格中檢索 SUM 值。查詢 1 計算特定員工在特定日期範圍內的總駕駛時間，而查詢 2 計算同一員工在同一期間的總工作時間。

嘗試將這些查詢組合成單一連接查詢會導致不正確的總和。這是因為 JOIN 操作創建了 bhds_timecard 和 bhds_mileage 表行的笛卡爾積。結果，駕駛時間和工作時間的 SUM 值乘以每張表中匹配行的數量。

為了解決這個問題，可以使用子查詢在連接表之前計算 SUM 值。透過將 SUM 操作移到單獨的子查詢中，可以防止笛卡爾積並獲得準確的結果。

以下是一個使用子查詢改進的查詢：

SELECT last_name, first_name, DATE_FORMAT(LEAST(mil_date, tm_date), '%m/%d/%y') AS dates, 
       total, minutes
FROM bhds_teachers AS i
LEFT JOIN (
    SELECT ds_id, YEARWEEK(mil_date) AS week, MIN(mil_date) AS mil_date, SUM(drive_time) AS minutes
    FROM bhds_mileage
    WHERE mil_date BETWEEN '2016-04-11' AND '2016-04-30'
    AND bhds_mileage.ds_id = 5
    GROUP BY ds_id, week
) AS m ON m.ds_id = i.ds_id
LEFT JOIN (
    SELECT ds_id, YEARWEEK(tm_date) AS week, MIN(tm_date) AS tm_date, SUM(tm_hours) AS total
    FROM bhds_timecard
    WHERE tm_date BETWEEN '2016-04-11' AND '2016-04-30' AND bhds_timecard.ds_id = 5
    GROUP BY ds_id, week
) AS t ON t.ds_id = i.ds_id AND t.week = m.week;

在這個查詢中：

• 子查詢 m 計算以員工 ID 和週分組的總駕駛時間。
• 子查詢 t 計算按員工 ID 和週分組的總工作時間。
• 主查詢將 bhds_teachers 表與子查詢 m 和 t 連接。

結果，查詢檢索員工和指定期間的姓氏、名字、日期、總工作時間和總駕駛時間，而不會出現 SUM 值相乘的問題。

以上是在 MySQL 中使用聚合函數連接表時如何避免 SUM 值相乘？的詳細內容。更多資訊請關注PHP中文網其他相關文章！