在 MYSQL 中加入带有 SUM 问题的表

2021-11-20 00:00:00 join sql mysql

我一直在获取连接表上的 SUM 时遇到问题，总是有问题，我可以通过运行两个查询来获得我需要的结果，我想知道这两个查询是否可以组合成一个连接查询，这里是我的查询以及我加入查询的尝试

I have always had trouble getting SUMs on join tables, there is always an issue, I can get the results I need by running two queries, I am wondering if this two queries can be combine to make one join query, here is the queries I have and my attempt to join the query

查询 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

以分钟为单位的输出是271,281,279

the output in minutes is 271, 281, 279

查询 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

这里的输出是 33.00, 36.00, 26.75

The output here is 33.00, 36.00, 26.75

现在我尝试加入查询

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

括号是预期的

输出 1044 (271), 1086 (281), 1215 (279)

this outputs 1044 (271), 1086 (281), 1215 (279)

推荐答案

当您在主查询中使用多个连接时，您最终会得到所有表的叉积，因此总和乘以匹配的行数在另一张桌子上.您需要将总和移动到子查询中.

When you use multiple joins in the main query, you end up with a cross product of all the tables, so the sums get multiplied by the number of rows matching in another table. You need to move the sums into subqueries.

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
    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

相关文章