每月获取记录，但如果该月没有记录，则为零

如果我有以下 SQL 表

Tests

id   type   receiveDate
 1   Blood   2012-01-18
 2   Blood   2012-01-20
 3   Blood   2012-01-18
 4   Blood   2012-03-01
 5   Blood   2012-05-21
 6   Blood   2012-05-18
 7   Blood   2012-06-14
 8   Blood   2012-07-22
 9   Blood   2012-07-29
 10  Blood   2012-12-04
 11  Blood   2012-12-30 

我运行查询

SELECT COUNT(*)
FROM Tests 
WHERE YEAR(receiveDate) = '2012' 
GROUP BY MONTH(receiveDate)

我会回来的

COUNT(*)
 3
 1
 2
 1
 2
 2

我的问题是，即使没有该月的记录，是否有办法取回所有十二个月？例如我想得到的输出

COUNT(*)
 3
 0
 1
 0
 2
 1
 2
 0
 0
 0
 0
 2

另外，作为额外的好处，有没有办法显示带有计数的月份？ 谢谢你！

SELECT  Months.m AS month, COUNT(Tests.receiveDate) AS total FROM 
(
    SELECT 1 as m 
    UNION SELECT 2 as m 
    UNION SELECT 3 as m 
    UNION SELECT 4 as m 
    UNION SELECT 5 as m 
    UNION SELECT 6 as m 
    UNION SELECT 7 as m 
    UNION SELECT 8 as m 
    UNION SELECT 9 as m 
    UNION SELECT 10 as m 
    UNION SELECT 11 as m 
    UNION SELECT 12 as m
) as Months
LEFT JOIN Tests  on Months.m = MONTH(Tests.receiveDate)  
GROUP BY
    Months.m

如果您想要具体年份，请尝试此操作。

SELECT  Months.m AS month, COUNT(Tests.receiveDate) AS total FROM 
(
    SELECT 1 as m 
    UNION SELECT 2 as m 
    UNION SELECT 3 as m 
    UNION SELECT 4 as m 
    UNION SELECT 5 as m 
    UNION SELECT 6 as m 
    UNION SELECT 7 as m 
    UNION SELECT 8 as m 
    UNION SELECT 9 as m 
    UNION SELECT 10 as m 
    UNION SELECT 11 as m 
    UNION SELECT 12 as m
) as Months
LEFT JOIN Tests on Months.m = MONTH(Tests.receiveDate)  
AND YEAR(Tests.receiveDate) = '2012'
GROUP BY Months.m