mysql - 包括 NULL 月份

Question

我无法获得正确的查询结果
我正在查询获取在相应月份注册了多少用户。到目前为止，我提出了以下查询，它允许我获得该结果，但没有显示没有用户注册的月份

SELECT 
YEAR(c.created_at) as 'YEAR',
(CASE WHEN MONTH(c.created_at)=1 THEN 'Jan' 
  WHEN (MONTH(c.created_at)=2) THEN 'Feb'
  WHEN (MONTH(c.created_at)=3) THEN 'Mar'
  WHEN (MONTH(c.created_at)=4) THEN 'Apr'
  WHEN (MONTH(c.created_at)=5) THEN 'May'
  WHEN (MONTH(c.created_at)=6) THEN 'Jun'
  WHEN (MONTH(c.created_at)=7) THEN 'Jul'
  WHEN (MONTH(c.created_at)=8) THEN 'Aug'
  WHEN (MONTH(c.created_at)=9) THEN 'Sep'
  WHEN (MONTH(c.created_at)=10) THEN 'Oct' 
  WHEN (MONTH(c.created_at)=11) THEN 'Nov'
  WHEN (MONTH(c.created_at)=12) THEN 'Dec'
END)
 as 'MONTH',
COUNT(c.id) as Total
FROM customers as c
WHERE YEAR(c.created_at) = 2016
GROUP BY c.id, MONTH(c.created_at)
ORDER BY YEAR(c.created_at), MONTH(c.created_at), Total ASC

结果:

+------+-------+-------+
| YEAR | MONTH | Total |
+------+-------+-------+
| 2016 | Feb   |     1 |
| 2016 | Apr   |     1 |
| 2016 | May   |     1 |
| 2016 | Jul   |     1 |
+------+-------+-------+
4 rows in set (0.00 sec)

我希望它是这样的

+------+-------+-------+
| YEAR | MONTH | Total |
+------+-------+-------+
| 2016 | Jan   |     0 |
| 2016 | Feb   |     1 |
| 2016 | Mar   |     0 |
| 2016 | Apr   |     1 |
| 2016 | May   |     1 |
| 2016 | Jun   |     0 |
| 2016 | Jul   |     1 |
| 2016 | Aug   |     0 |
| 2016 | Sep   |     0 |
| 2016 | Oct   |     0 |
| 2016 | Nov   |     0 |
| 2016 | Dec   |     0 |
+------+-------+-------+

Answer 1

这样试试

使用 SUM 而不是 COUNT。无论如何你要显示零或一个。

 SELECT 
    YEAR(c.created_at) as 'YEAR',
    (CASE WHEN MONTH(c.created_at)=1 THEN 'Jan' 
      WHEN (MONTH(c.created_at)=2) THEN 'Feb'
      WHEN (MONTH(c.created_at)=3) THEN 'Mar'
      WHEN (MONTH(c.created_at)=4) THEN 'Apr'
      WHEN (MONTH(c.created_at)=5) THEN 'May'
      WHEN (MONTH(c.created_at)=6) THEN 'Jun'
      WHEN (MONTH(c.created_at)=7) THEN 'Jul'
      WHEN (MONTH(c.created_at)=8) THEN 'Aug'
      WHEN (MONTH(c.created_at)=9) THEN 'Sep'
      WHEN (MONTH(c.created_at)=10) THEN 'Oct' 
      WHEN (MONTH(c.created_at)=11) THEN 'Nov'
      WHEN (MONTH(c.created_at)=12) THEN 'Dec'
    END) AS 'MONTH',
    SUM (CASE WHEN c.id IS NOT NULL THEN 1 ELSE 0 END ) as Total
 FROM customers as c
 WHERE YEAR(c.created_at) = 2016
 GROUP BY c.id, MONTH(c.created_at)
 ORDER BY YEAR(c.created_at), MONTH(c.created_at), Total ASC