如何按 DATE 选择用户表的填充作为递增值

Question

考虑我们Users表的摘录：

+-------------+---------------------+---------------------+
| id          | last_login          | created             |
+-------------+---------------------+---------------------+
| 14551578822 | 2014-02-22 17:38:39 | 2013-03-26 23:30:50 |
| 18442388426 | 0000-00-00 00:00:00 | 2013-11-07 15:51:11 |
| 49983341634 | 2014-03-06 22:28:47 | 2013-03-23 16:00:05 |
|  9527246957 | 2014-01-17 02:37:53 | 2013-05-14 02:14:49 |
| 58667409337 | 2014-03-08 06:54:01 | 2013-05-15 01:52:23 |
|  1907780002 | 2014-03-01 03:24:04 | 2013-05-01 07:57:56 |
| 65319490251 | 2014-03-19 05:49:41 | 2013-03-23 08:53:43 |
| 23896465717 | 0000-00-00 00:00:00 | 2012-10-21 10:52:23 |
| 19147401900 | 0000-00-00 00:00:00 | 2013-05-01 17:43:28 |
| 28598429318 | 0000-00-00 00:00:00 | 2014-03-14 14:44:15 |
+-------------+---------------------+---------------------+

我们有很多很多用户 - 我们希望生成一份报告，该报告将显示随着日期的增加而拥有的用户总数。我们希望输出类似如下：

    +---------+---------------+
    |  DATE   |   User Count  |
    +---------+---------------+
    | 2012-08 |      122      |
    | 2012-09 |     1746      |
    | 2012-10 |     3847      |
    | 2012-11 |     5826      |
        ...
    | 2014-03 |   472647      |
    | 2014-04 |   497286      |
    +---------+---------------+

一定有某种方法可以做到这一点，没有子选择和各种混乱。我已经有一个表，通过以下查询显示每个周期的连接数：

SELECT DATE(users.created) as JOIN_DATE , COUNT(users.id) AS JOIN_COUNT from users
WHERE users.created > '2012-07-01 00:00:00'
GROUP BY JOIN_DATE
ORDER BY JOIN_DATE ASC

只是想知道是否有办法做到这一点。

谢谢！

Answer 1

您可以使用变量来汇总每次迭代的总体

SELECT t.date , 
@population := @population+t.per_time population
FROM (
SELECT 
DATE_FORMAT(`last_login` ,'%Y-%m') `date`,
COUNT(*) per_time
FROM  Table1
  WHERE created > '2012-07-01 00:00:00'
GROUP BY `date` ) t ,
(SELECT @population:=0) p

小提琴演示