MySQL 滚动行数

Question

我正在尝试对学生网站上的注册增长进行滚动计数。

查询如下所示:

SELECT COUNT(type) as student_count, MONTH(created_at) as month, YEAR(created_at) as year
FROM users
WHERE type = 'student'
GROUP BY MONTH(created_at), YEAR(created_at)
ORDER BY year, month

这会产生以下输出:

我在查询中试图实现的是继续从前面的行中添加 student_counts。

所以:

2014 年 12 月应有 15 名学生

2015年1月应有16名学生

2015年2月应该有34个学生

等等……

这在 SQL 中可行还是在代码本身输出数据时更好？

Answer 1

select *, @sum := @sum + student_count as sum
from
(
    SELECT YEAR(created_at) as year, 
           MONTH(created_at) as month, 
           COUNT(type) as student_count
    FROM users
    WHERE type = 'student'
    GROUP BY year, month
    ORDER BY year, month
) tmp
CROSS JOIN (select @sum := 0) s