MySQL 查询返回每个月之前创建的所有行

Question

我有一个用户表:

|id |created_at|
|---|----------|
|1  |2016-11-15|
|2  |2016-08-30|
|3  |2016-06-23|
|4  |2015-12-03|

我想运行一个查询，返回每个月以及截至该月已创建的行数。我现在的查询是:

SELECT YEAR(created_at) AS `year`, MONTH(created_at) AS `month`, COUNT(*) AS `count`
FROM users
GROUP BY `year`, `month`
ORDER BY `year` DESC, `month` DESC

返回下表:

|year|month|count|
|----|-----|-----|
|2016|11   |1    |
|2016|8    |1    |
|2016|6    |1    |
|2015|12   |1    |

我想要的表格如下所示:

|year|month|count|
|----|-----|-----|
|2016|11   |4    |
|2016|8    |3    |
|2016|6    |2    |
|2015|12   |1    |

或者更好:

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

Answer 1

尝试测试一下。

SELECT YEAR(Created) as Year, MONTH(Created) as Month,
      count(*) AS num_daily_interactions,
       (
          SELECT 
             COUNT(*)
          FROM users l2
          WHERE DATE(l2.created_at) <= l1.created_at
       ) as total_interactions_per_day

    FROM `users ` as l1

    GROUP BY YEAR(created_at), MONTH(created_at)
    ORDER BY YEAR(created_at) DESC, MONTH(created_at) DESC;