mysql - 如何选择 User 表的人口作为按 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

Fiddle Demo