为什么连接多个表时 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中文网其他相关文章！