mysql - 月份分组，不返回没有记录的月份

Question

我有一个疑问:

select c.MonthNo,c.YearNo 
from (SELECT month(created_at) as MonthNo,
             Year(created_At) as YearNO,
             count(*) as total
      FROM users u 
      where created_at between '2014-02-01 00:00:00' and '2015-01-06 23:59:59' 
      group by monthNo order by yearNO,monthNo) as c;

结果:

MonthNo  |  YearNo
---------+--------
  2      |  2014
  3      |  2014
  4      |  2014
  5      |  2014
  6      |  2014
  7      |  2014
  8      |  2014
  9      |  2014
  10     |  2014      
  11     |  2014
  12     |  2014

我的问题是查询没有返回当前月份，即 2015 年 1 月。我认为这是因为它没有任何记录。在这种情况下我希望它返回 0。

Answer 1

创建日历表。

WITH calender
     AS (SELECT Cast('20140101' AS DATE) AS [dates] -- startdate
         UNION ALL
         SELECT Dateadd(dd, 1, [dates])
         FROM   calender
         WHERE  Dateadd(dd, 1, [dates]) <= '20151231' -- Endate
        )
SELECT Month(c.dates) AS MonthNo,
       Year(c.dates)  AS YearNO,
       Count(*)       AS total
FROM   calender C
       LEFT JOIN users u
              ON Month(c.dates) = Month(created_at)
                 AND Year(c.dates) = Year(created_at)
WHERE  created_at BETWEEN '2014-02-01 00:00:00' AND '2015-01-06 23:59:59'
GROUP  BY Month(c.dates),
          Year(c.dates)
ORDER  BY Month(c.dates),
          Year(c.dates)
OPTION (maxrecursion 0)