Mysql计数时得到零值

Question

我正在尝试计算一段时间内已取消的销售订单数。但是我遇到了它不返回零结果的问题

我的 table

+---------------+------------+------------------+
| metrausername | signupdate | cancellationdate |
+---------------+------------+------------------+
| GLO00026      | 2017-06-22 | 2017-03-20       |
| GLO00055      | 2017-06-22 | 2017-04-18       |
| GLO00022      | 2017-06-27 | NULL             |
| GLO00044      | 2017-06-24 | NULL             |
| GLO00005      | 2017-06-26 | NULL             |
+---------------+------------+------------------+

我正在尝试计算的语句

SELECT metrausername, COUNT(*) AS count FROM salesdata2 
WHERE cancellationdate IS NOT NULL 
AND signupDate >= '2017-6-21' AND signupDate <= '2017-7-20' 
GROUP BY metrausername;

如果有任何其他信息有帮助，请告诉我

Answer 1

如果metrausername被where过滤掉了，就不会出现。左连接到聚合来绕过这个:

select distinct a1.metrausername, coalesce(a2.counted,0) as counted -- coalesce replaces null with a value
from salesdata2  a1
left join
(
SELECT metrausername, COUNT(*) AS counted
FROM salesdata2 
WHERE cancellationdate IS NOT NULL 
AND signupDate >= '2017-6-21' AND signupDate <= '2017-7-20' 
GROUP BY metrausername
) a2
on a1.metrausername = a2.metrausername