在 MySQL 中使用聚合函数连接表时如何避免 SUM 值相乘？

避免 MySQL 中使用聚合函数连接表时 SUM 值相乘的技巧

在 MySQL 查询中组合多个连接操作时，如果使用 SUM 等聚合函数，可能会导致意外结果。这是因为连接表的行笛卡尔积会使求和结果相乘，从而导致结果不准确。

假设存在一个例子，最初使用两个查询从不同的表中检索 SUM 值。查询 1 计算特定员工在特定日期范围内的总驾驶时间，而查询 2 计算同一员工在同一期间的总工作时间。

尝试将这些查询组合成单个连接查询会导致不正确的总和。这是因为 JOIN 操作创建了 bhds_timecard 和 bhds_mileage 表行的笛卡尔积。结果，驾驶时间和工作时间的 SUM 值乘以每张表中匹配行的数量。

为了解决这个问题，可以使用子查询在连接表之前计算 SUM 值。通过将 SUM 操作移到单独的子查询中，可以防止笛卡尔积并获得准确的结果。

以下是一个使用子查询改进的查询：

<code class="language-sql">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;</code>

在这个查询中：

• 子查询 m 计算按员工 ID 和周分组的总驾驶时间。
• 子查询 t 计算按员工 ID 和周分组的总工作时间。
• 主查询将 bhds_teachers 表与子查询 m 和 t 连接。

结果，查询检索员工和指定期间的姓氏、名字、日期、总工作时间和总驾驶时间，而不会出现 SUM 值相乘的问题。

以上是在 MySQL 中使用聚合函数连接表时如何避免 SUM 值相乘？的详细内容。更多信息请关注PHP中文网其他相关文章！