我总是很难在联接表上求和,总是有一个问题,我可以通过运行两个查询来获得所需的结果,我想知道这两个查询是否可以合并为一个联接查询,下面是我的查询和我尝试联接查询的结果
查询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
以分钟为单位的输出是271281279
查询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)
2条答案
按热度按时间clj7thdc1#
有几个问题。。。之间的部分笛卡尔积(叉积)
bhds_mileage
以及bhds_timecard
,因为一个表中的每个详图行(在组中)都将与另一个表中的详图行“交叉连接”。这是在groupby操作折叠行并计算总和之前发生的。这就解释了为什么你会看到“膨胀”的价值观。解决方法是在内联视图中计算至少一个sum()聚合。。。像第一个查询一样完成sum()/group by()。为清楚起见,您可以对两个原始查询执行相同的操作,然后将内联视图的结果连接起来。
mysql本机不支持完全外部连接。其中一张table必须是驾驶台。例如,我们可以使用
_timecard
作为驾驶台,但这将意味着我们必须返回一个给定的星期从_timecard
以便从\中返回相应的行。也就是说,没有争吵_timecard
,我们无法从_mileage
.我们注意到
bhds_teacher
是外部连接。如果我们在ds_id
两者都有_mileage
以及_timecard
,引用_teacher
,那么就不一定需要外部连接,我们可以使用内部连接,然后使用_teacher
作为两个外部连接的驱动台。另一个问题是选择列表中的非聚合。。。例如。
DATE_FORMAT((tm_date), '%m/%d/%y')
分组依据是年和周,因此日期格式的值是不确定的。。。它可能来自任何地方tm_date
在集团内部。不能保证你会得到一周中的第一天,一周中最早的日期或者其他什么。另外,第二个参数
WEEK
函数被省略,因此将默认为default_week_format
系统变量。就我个人而言,我会避免YEAR
,WEEK
以及CONCAT
函数,并使用更简单的DATE_FORMAT
,使用明确包含周的模式参数的日期格式字符串。如果要在“week”上连接,那么连接 predicate 应该在“week”值上,而不是在一周内的一个不确定日期上。
(数据上可能有一些我们不知道的特定限制。。。如果在某个星期(星期一)的里程数中有行,那么我们保证在同一个星期一有一张考勤卡。在更一般的情况下,我们没有这种保证。)
即使我们有这个保证,我们也不能保证select列表中的非聚合不会返回星期二时间卡和星期四里程的日期(除非有某种形式的保证,数据将只包括带有“星期一”日期的行(时间卡和里程)。否则,非聚合表达式就不是连接 predicate 的可靠表达式。
假设
ds_id
是唯一的_teacher
,并由外键引用ds_id
从两者_mileage
以及_timecard
,然后是这样的:wrrgggsh2#
当您在主查询中使用多个联接时,最终会得到所有表的叉积,因此总和将乘以另一个表中匹配的行数。您需要将总和移动到子查询中。