MySql查询每月返回0如果为空

Question

如果我不放置 where 子句，我的查询就可以了。但我必须有 where 子句，因为我将按用户获得每月总数。以下是我的查询:

SELECT IFNULL(count(PropertyId), 0) AS Total, a.monthLead
FROM (SELECT 'May' monthLead, 1 monthOrder UNION ALL SELECT 'Jun' monthLead, 2 monthOrder UNION ALL SELECT 'Jul' monthLead, 3 monthOrder UNION ALL SELECT 'Aug' monthLead, 4 monthOrder UNION ALL SELECT 'Sep' monthLead, 5 monthOrder UNION ALL SELECT 'Oct' monthLead, 6 monthOrder UNION ALL SELECT 'Nov' monthLead, 7 monthOrder UNION ALL SELECT 'Dec' monthLead, 8 monthOrder) a
LEFT JOIN leads b ON a.monthLead = DATE_FORMAT(b.DateEntered, '%b')
LEFT JOIN users_leads c ON c.LeadId = b.PropertyId
WHERE c.UserId = 78 AND b.IsRecordDeleted = 0
AND DATE_FORMAT(b.DateEntered, '%b') IN ('May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')
GROUP BY a.monthLead, year(b.DateEntered), month(b.DateEntered)
ORDER BY a.monthOrder, b.DateEntered

输出是:

-----------------------
-- Total | monthLead --
-- 2     | May       --
-- 2     | Sep       --
-----------------------

应该是:

-----------------------
-- Total | monthLead --
-- 2     | May       -- 
-- 0     | Jun       --
-- 0     | Jul       --
-- 0     | Aug       --
-- 2     | Sep       --
-- 0     | Nov       --
-- 0     | Dec       --
-----------------------

我已将 AND 更改为 OR 但它不正确。如果我使用 OR，它将获得其他用户的线索。

这是 SQLFiddle:http://sqlfiddle.com/#!2/acf86/2

谁能帮帮我？

谢谢

Answer 1

这应该是你想要的:

SELECT IFNULL(count(b.PropertyId), 0) AS Total, a.monthLead
FROM (SELECT 'May' monthLead, 1 monthOrder UNION ALL SELECT 'Jun' monthLead, 2 monthOrder UNION ALL SELECT 'Jul' monthLead, 3 monthOrder UNION ALL SELECT 'Aug' monthLead, 4 monthOrder UNION ALL SELECT 'Sep' monthLead, 5 monthOrder UNION ALL SELECT 'Oct' monthLead, 6 monthOrder UNION ALL SELECT 'Nov' monthLead, 7 monthOrder UNION ALL SELECT 'Dec' monthLead, 8 monthOrder) a
LEFT JOIN (
  SELECT b.DateEntered, b.PropertyId /*, c.UserId, b.IsRecordDeleted -- not necessary*/
  FROM leads b, users_leads c
  WHERE c.PropertyId = b.PropertyId
  AND c.UserId = 78 AND b.IsRecordDeleted = 0
  AND DATE_FORMAT(b.DateEntered, '%b') IN ('May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')
) b ON a.monthLead = DATE_FORMAT(b.DateEntered, '%b')
GROUP BY a.monthLead, year(b.DateEntered), month(b.DateEntered)
ORDER BY a.monthOrder, b.DateEntered

fiddle :http://sqlfiddle.com/#!2/acf86/35

另请注意，以下行不是必需的:

AND DATE_FORMAT(b.DateEntered, '%b') IN ('May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')

因为记录无论如何都会被 a.monthLead = DATE_FORMAT(b.DateEntered, '%b') 过滤。

基本的语义思想是要非常小心你要外连接的东西。在我的查询中，我只使用了一个左连接，因为我想对输入表进行内部连接并确保当没有来自 leads 的记录时，也没有来自 user_leads 的记录。

当内部联接查询中没有记录时，此结果然后与您的查询中表 A 左联接以生成空记录。