Join tables with SUM issue in MYSQL(在 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
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
the output in minutes is 271, 281, 279
Query 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
The output here is 33.00, 36.00, 26.75
Now my attempt to join the query
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
parenthesis is what is expected
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
这篇关于在 MYSQL 中加入具有 SUM 问题的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:在 MYSQL 中加入具有 SUM 问题的表


- 在SQL中,如何为每个组选择前2行 2021-01-01
- 如何将 SonarQube 6.7 从 MySQL 迁移到 postgresql 2022-01-01
- 远程 mySQL 连接抛出“无法使用旧的不安全身份验证连接到 MySQL 4.1+"来自 XAMPP 的错误 2022-01-01
- 以一个值为轴心,但将一行上的数据按另一行分组? 2022-01-01
- 如何使用 pip 安装 Python MySQLdb 模块? 2021-01-01
- 导入具有可变标题的 Excel 文件 2021-01-01
- SQL 临时表问题 2022-01-01
- 如何将 Byte[] 插入 SQL Server VARBINARY 列 2021-01-01
- 使用 Oracle PL/SQL developer 生成测试数据 2021-01-01
- 更改自动增量起始编号? 2021-01-01