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

本教程将介绍在 MYSQL 中加入具有 SUM 问题的表的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

在 MYSQL 中加入具有 SUM 问题的表 教程 第1张

问题描述

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 问题的表的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。