sql - 如何在查询中包含零计数结果

Question

我有这两张表:

运营商:

Id Nome
--+----
 1 JDOE
 2 RROE
 3 MMOE

来电:

Id CallDate OpId
--+--------+----
 1 20161228    2
 2 20161228    3
 3 20161228    2
 4 20161228    3
 5 20170104    1
 6 20170104    2
 7 20170104    1    

这个查询:

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON Operators.id = Calls.OpId
GROUP BY Calls.CallDate, Operators.id, Operators.Nome
HAVING Calls.CallDate=20170104;

返回:

Id Nome CountCalls
--+----+----------
 1 JDOE          2
 2 RROE          1

我怎样才能让它返回这个呢？

Id Nome CountCalls
--+----+----------
 1 JDOE          2
 2 RROE          1
 3 MMOE          0

也就是说，如何在任何查询中也包括在左连接表中没有出现的主表的零结果，至少在查询过滤条件定义的数据切片中？

这是 Access 2013。

我已阅读 this answer但看不出它与我正在做的事情有何不同。

Answer 1

因为你有一个引用 Calls.CallDate在您的 HAVING子句，您将删除没有调用的运算符。如果没有来电，则 CallDate将是 NULL , 和 NULL=20170104不正确，因此这些行被排除在外。您需要将此谓词移动到您的连接子句:

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON (Operators.id = Calls.OpId AND Calls.CallDate=20170104)
GROUP BY Calls.CallDate, Operators.id, Operators.Nome;

您也不需要按 Calls.CallDate 分组，因为无论如何你只有一个，所以你可以使用:

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON (Operators.id = Calls.OpId AND Calls.CallDate=20170104)
GROUP BY Operators.id, Operators.Nome;

顺便说一句 HAVING是错误的运算符。 HAVING用于过滤聚合，因为你不过滤聚合，你应该简单地使用 WHERE

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON Operators.id = Calls.OpId
WHERE Calls.CallDate=20170104
GROUP BY Calls.CallDate, Operators.id, Operators.Nome;

您将使用 HAVING如果你想过滤 CountCalls ，例如，如果您只想要调用超过 1 次电话的运营商，您可以使用:

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON Operators.id = Calls.OpId
WHERE Calls.CallDate=20170104
GROUP BY Calls.CallDate, Operators.id, Operators.Nome
HAVING Count(Calls.OpId) > 1;

这只会返回

Id Nome CountCalls
--+----+----------
 1 JDOE          2