即使没有对应的mysql结果也如何计数？

Question

我正在对查询进行形式化，以按日期排序提供过去一年中提交的报告数量。我用php获取当前年份和月份：

$year = date('Y') - 1;
$month = date('m');

并执行以下查询：SQL：

SELECT month(date_lm) AS `month` ,
count(*) AS `count`
FROM `reports` 
WHERE (status = 'submitted') 
AND (date_lm > 2012-08) 
GROUP BY month(date_lm) 
ORDER BY month(date_lm) ASC

而且由于去年只提交了1个结果，因此我只得到了1个结果…

| month  |  count  |
|   7    |    1    |

但是我希望结果集显示出来：

| month  |  count  |
|   9    |    0    |
|   10   |    0    |
|   11   |    0    |
|   12   |    0    |
|   1    |    0    |
|   2    |    0    |
|   3    |    0    |
|   4    |    0    |
|   5    |    0    |
|   6    |    0    |
|   7    |    1    |
|   8    |    0    |

那可能吗？

Answer 1

您应该将此表与1..12表保持联接。像这样的东西：

SELECT  Months.id AS `month` ,
COUNT(`reports`.date_lm) AS `count`
FROM 
(
  SELECT 1 as ID UNION SELECT 2 as ID UNION  SELECT 3 as ID UNION SELECT 4 as ID 
  UNION  
  SELECT 5 as ID UNION SELECT 6 as ID UNION SELECT 7 as ID UNION SELECT 8 as ID 
  UNION  
  SELECT 9 as ID UNION SELECT 10 as ID UNION SELECT 11 as ID UNION SELECT 12 as ID
) as Months
LEFT JOIN `reports` on Months.id=month(`reports`.date_lm)
                       AND 
                       (status = 'submitted') 
                       AND (date_lm > 2012-08)
GROUP BY Months.id 
ORDER BY Months.id ASC

SQL Fiddle演示