為什麼連接多個表時 MySQL SUM() 結果不正確？

MySQL SUM() 和多表連接：避免不正確的聚合

在 MySQL 中將 SUM() 計算與多個表連接相結合需要仔細考慮，以防止結果不準確。連接期間的笛卡爾積效應會產生一個常見的陷阱，導致總和膨脹。

問題：SUM() 結果膨脹

使用者嘗試將兩個查詢（每個查詢從不同表計算總和）合併到一個連線查詢。

查詢 1（里程）： 依老師分組的每週駕駛時間（分鐘）總和。

<code class="language-sql">SELECT last_name, first_name, ..., SUM(drive_time) AS MINUTES
FROM bhds_mileage ...
WHERE mil_date BETWEEN ... AND ...
GROUP BY ...</code>

查詢 2（考勤卡）： 依教師分組的每週總小時數。

<code class="language-sql">SELECT last_name, first_name, ..., SUM(tm_hours) AS total
FROM bhds_timecard ...
WHERE tm_date BETWEEN ... AND ...
GROUP BY ...</code>

加入嘗試失敗：

使用者嘗試組合這些查詢直接導致了錯誤的總和：

<code class="language-sql">SELECT last_name, first_name, ..., SUM(tm_hours) AS total, SUM(drive_time) AS MINUTES
FROM bhds_timecard ...
LEFT JOIN bhds_mileage ...
ON ...
WHERE ...
GROUP BY ...</code>

問題？ SUM() 函數在 連接之後應用 ，由於連接表之間可能存在多個匹配行，導致總和相乘。

解：預聚合子查詢

正確的方法是在加入之前預先聚合子查詢中的總和：

<code class="language-sql">SELECT last_name, first_name, ..., total, minutes
FROM bhds_teachers ...
LEFT JOIN (
    -- Subquery 1: Mileage SUM
    SELECT teacher_id, SUM(drive_time) AS minutes, ...
    FROM bhds_mileage ...
    WHERE mil_date BETWEEN ... AND ...
    GROUP BY teacher_id
) AS m ON ...
LEFT JOIN (
    -- Subquery 2: Timecard SUM
    SELECT teacher_id, SUM(tm_hours) AS total, ...
    FROM bhds_timecard ...
    WHERE tm_date BETWEEN ... AND ...
    GROUP BY teacher_id
) AS t ON ...</code>

透過在單獨的子查詢中執行SUM()操作，我們確保每個表的資料在連接發生之前正確聚合，從而防止總和相乘並產生準確的結果。 teacher_id（或等效項）對於正確的連接條件至關重要。

以上是為什麼連接多個表時 MySQL SUM() 結果不正確？的詳細內容。更多資訊請關注PHP中文網其他相關文章！