如何正确对 MySQL 中连接表的聚合值求和以避免交叉乘积问题？

在 MySQL 中使用 SUM 聚合连接表：解决交叉产品问题

在 MySQL 中，使用 SUM 聚合函数组合两个查询通常会带来挑战。表之间的交叉乘积可能导致 SUM 值不正确。为了解决这个问题，需要将 SUM 函数封装到子查询中。

考虑以下示例，其中两个查询分别针对特定日期和教师 ID 为 5 的情况检索 SUM(drive_time) 和 SUM(tm_hours)：

查询 1：

SELECT last_name, first_name, DATE_FORMAT(mil_date, '%m/%d/%y') AS dates, 
SUM(drive_time) MINUTES 
FROM bhds_mileage 
LEFT JOIN bhds_teachers i 
ON i.ds_id = bhds_mileage.ds_id 
WHERE mil_date BETWEEN '2016-04-11' AND '2016-04-30' 
AND bhds_mileage.ds_id = 5 
GROUP BY CONCAT(YEAR(mil_date), '/', WEEK(mil_date)), bhds_mileage.ds_id 
ORDER BY last_name ASC, dates ASC

查询 2：

SELECT last_name, first_name, DATE_FORMAT(tm_date, '%m/%d/%y') AS dates,   
SUM(tm_hours) total 
FROM bhds_timecard 
LEFT JOIN bhds_teachers i 
ON i.ds_id = bhds_timecard.ds_id 
WHERE tm_date BETWEEN '2016-04-11' AND '2016-04-30' AND bhds_timecard.ds_id = 5 
GROUP BY CONCAT(YEAR(tm_date), '/', WEEK(tm_date)), bhds_timecard.ds_id 
ORDER BY last_name ASC, dates ASC

简单的连接尝试：

要组合这些查询，一种简单的方法是将它们连接如下：

SELECT last_name, first_name, DATE_FORMAT(tm_date, '%m/%d/%y') AS dates,  
SUM(tm_hours) total, SUM(drive_time) MINUTES 
FROM bhds_timecard 
LEFT JOIN bhds_teachers i 
ON i.ds_id = bhds_timecard.ds_id 
LEFT JOIN bhds_mileage 
ON DATE_FORMAT(bhds_timecard.tm_date, '%m/%d/%y') = DATE_FORMAT(bhds_mileage.mil_date, '%m/%d/%y') AND bhds_timecard.ds_id = bhds_mileage.ds_id 
WHERE tm_date BETWEEN '2016-04-11' AND '2016-04-30' AND bhds_timecard.ds_id = 5 
GROUP BY CONCAT(YEAR(tm_date), '/', WEEK(tm_date)), bhds_timecard.ds_id 

但是，这种方法会在表之间创建交叉乘积，导致 SUM 值不正确。

解决方案：

为了获得正确的 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 

以上是如何正确对 MySQL 中连接表的聚合值求和以避免交叉乘积问题？的详细内容。更多信息请关注PHP中文网其他相关文章！