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

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

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

现在我尝试加入查询

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)

最佳答案

在主查询中使用多个联接时,最终会得到所有表的叉积,因此总和将乘以另一个表中匹配的行数。您需要将总和移到子查询中。

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

10-06 12:08