我创建了一个日历表,其中仅包含许多日期。然后,我的活动表中的日期会对齐,如果一天没有活动,我想为此返回零。我有以下内容:
SELECT cDate, Branch, IFNULL(COUNT(*),0) as count
FROM Events E LEFT JOIN Calendar C ON C.cDate = DATE(E.eventDate)
WHERE cDate BETWEEN '2018-04-14' AND '2018-04-18'
GROUP BY Branch, cDate
ORDER BY cDate
但是结果目前显示:
cDate | Branch | count
2018-04-14 | 1 | 5
2018-04-14 | 2 | 4
2018-04-16 | 1 | 1
2018-04-16 | 2 | 3
2018-04-17 | 1 | 5
2018-04-18 | 1 | 4
但是我打算让它显示任何计数为零的日期,如下所示:
cDate | Branch | count
2018-04-14 | 1 | 5
2018-04-14 | 2 | 4
2018-04-15 | 1 | 0
2018-04-15 | 2 | 0
2018-04-16 | 1 | 1
2018-04-16 | 2 | 3
2018-04-17 | 1 | 5
2018-04-17 | 2 | 0
2018-04-18 | 1 | 4
2018-04-18 | 2 | 0
WHERE
子句中要求外部联接表中的列为非NULL的任何条件有效地“否定”联接的外部性,使其等效于内部联接。
这种情况
cdate BETWEEN '2018-04-14' AND '2018-04-18'
只有具有非NULL值的行才能满足cdate
。
这样可以帮助我思考左外部联接操作:
当左边的一行没有右边的匹配行时,在右边发明了一个虚拟行作为匹配行。(联接需要匹配的行,以便可以返回该行。)生成/发明的虚拟行完全由NULL
值组成。
因此,要解决的部分问题是将该条件从WHERE
子句重新定位到ON
外部联接的子句中。
解决此问题可能是解决问题所需的全部,但是...我犹豫要特别建议将其作为解决方案,因为我对实际的规范不了解。
另一个建议:
为了帮助将来的读者,请考虑对所有列引用进行限定。(我们注意到该SQL语句已经为表分配了别名。)
根据问题中发布的信息,我们无法确定该branch
列来自哪个表。看起来Calendar
可能只是唯一日期的列表,所以我们将假定branch
在Event
表中找到该列。
我怀疑期望的结果将通过如下查询返回:
SELECT c.cdate
, b.branch
, COUNT(e.branch) AS `count`
FROM Calendar c
CROSS
JOIN Branch b
LEFT
JOIN Events e
ON e.eventdate >= c.cdate
AND e.eventdate < c.cdate + INTERVAL 1 DAY
AND e.branch = b.branch
WHERE c.cdate BETWEEN '2018-04-14' AND '2018-04-18'
GROUP
BY c.cdate
, b.branch
ORDER
BY c.cdate
, b.branch
让我们打开包装。
我们正在从中获取指定范围内的所有日期Calendar
。(我们怀疑/假设这cdate
是DATE数据类型,并且保证是唯一的。在此查询中,我们基本上是在使用它Calendar
来生成一组连续的日期值。)
而且我们想从中获取Events
与每个特定日期相关的数目的“计数” Calendar
。
请注意,COUNT()
聚合将返回非NULL值;如果我们要计算一个计算结果为NULL的表达式,则该计数不会增加。我们不需要将COUNT()
聚合包装在IFNULL / COALESCE / CASE中,以将NULL替换为零。
我们正在做“左联接”。这意味着我们希望驱动表(Calendar
在本例中)位于左侧,而我们希望从中找到匹配项的表位于右侧。如果匹配行不右侧发现,包括所有NULL值的虚设行将被“生成”,所以加入行可以返回。
由于我们要通过“ cdate
”和“ branch
”来获取计数,因此我们还需要“ branch
”值的行源。(如@Shadow所述,我们可以使用表格代替内联视图b
。内联视图的目的b
是获取branch
我们要返回的值的不同列表。)
这CROSS JOIN
将使我们获得交叉产品。也就是说,所有cdate
值都与所有值匹配branch
,因此我们有一个完整的集合。五个cdate
值,两个branch
值,使我们得到一组10行,即我们要返回的行。当Event
给定cdate
和没有匹配的行时,我们需要这些行能够返回“零”计数branch
。
再一次,我们假设在中cdate
是唯一的Calendar
,因此我们从返回(最多)五行Calendar
。我们还假设会有(潜在)从更多的行Event
要检查需要,比较eventdate
到cdate
。我们不想阻止MySQL有效地利用列的索引范围操作eventdate
(有合适的索引),因此我们避免将eventdate
列包装在函数中,而是引用裸列。
我们只是在猜测需求,因此我的建议可能无法满足实际要求。
跟进
我们需要一个行源作为branch
值。可以是表,也可以是内联视图查询。原始SQL并未假设使用Branch
表,因此我们使用查询来获取不同的分支列表:
JOIN ( SELECT br.branch
FROM Events br
GROUP BY br.branch
) b
我的原始答案中的内联视图查询的作用与Branch
修改后的查询中的表相同。它返回表中branch
出现的值的不同列表Events
。如果有一个索引branch
作为前导列,则MySQL可以使用该索引。
最大的区别branch
在于Branch
表中出现但表中未出现的值(例如3)Event
。使用的内联视图Event
,我们将不会返回branch
= 3的任何行。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句