php - 如何计算每年每个月的行数，即使一个月不存在

Question

我正在尝试计算每年每个月的行数(表格中有几年)而不跳过空月。说我的表有一个月没有数据。像 1,2,5,6,7,11,12(缺少 3,4,8,9,10)。但我不希望输出跳过它 - 只需输入 0。

我想出了这段代码，但它跳过了空记录。

SELECT
    YEAR(`log_datetime`) as year,MONTH(`log_datetime`) AS mon,
    count(log_id) AS count
FROM
    log_db
GROUP BY
    MONTH (`log_datetime`),
    YEAR (`log_datetime`)
ORDER BY
    YEAR (`log_datetime`) DESC,
    MONTH (`log_datetime`) DESC

我期望的是:

+------+----+-----+
| 2015 | 12 | 100 |
+------+----+-----+
| 2015 | 11 | 120 |
+------+----+-----+
| 2015 | 10 |  0  |
+------+----+-----+
| 2015 | 09 |  0  |
+------+----+-----+
| 2015 | 08 |  0  |
+------+----+-----+
| 2015 | 07 | 123 |
+------+----+-----+
| 2015 | 06 | 200 |
+------+----+-----+
| 2015 | 05 | 250 |
+------+----+-----+
| 2015 | 04 |  0  |
+------+----+-----+
| 2015 | 03 |  0  |
+------+----+-----+
| 2015 | 02 | 211 |
+------+----+-----+
| 2015 | 01 | 200 |
+------+----+-----+

Answer 1

您需要一个日历 表来执行此操作。

SELECT c.years,
       c.months, 
       Count(log_id) AS count 
FROM   calendar_table C 
       LEFT OUTER JOIN log_db l 
                    ON c.months = Month (`log_datetime`) 
                       AND c.years = Year (`log_datetime`) 
GROUP  BY c.years, 
          c.months 
ORDER  BY c.months DESC, 
          c.years DESC 

引用以下链接生成日历表

1. How to populate a table with a range of dates?
2. https://www.brianshowalter.com/calendar_tables
3. https://gist.github.com/bryhal/4129042