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

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不是真，因此这些行被排除在外。您需要将该谓词移动到join子句：

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上使用fliter，例如，如果您只想使用超过一个呼叫的操作符，您可以使用CountCalls：

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

Answer 2

请尝试以下查询：

SELECT o.id, o.name, count(c.opid) as countcalls
FROM Operators o LEFT JOIN calls c ON o.id = c.opid AND c.calldate=20170104
GROUP BY c.calldate, o.id, o.name
ORDER BY o.id;

输出：

+------+------+------------+
| id   | name | countcalls |
+------+------+------------+
|    1 | JDOE |          2 |
|    2 | RROE |          1 |
|    3 | MMOE |          0 |
+------+------+------------+