MySQL滚动行数

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

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

SELECT @cumulative := 0;
SELECT @cumulative := @cumulative + student_count student_count,
       month, year
FROM (
    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)
) A ORDER BY year, month

在MySQL中处理这个问题的一种方法是使用相关的子查询来查找正在运行的总数。

SELECT DISTINCT
    (SELECT COUNT(*) FROM users u2
     WHERE DATE_FORMAT(u2.created_at, '%Y-%m') <=
           DATE_FORMAT(u1.created_at, '%Y-%m')) AS student_count,
    DATE_FORMAT(created_at, '%Y-%m') AS ym
FROM users u1
WHERE type = 'student'
ORDER BY DATE_FORMAT(created_at, '%Y-%m');

Demo

这里没什么要解释的,除了这个 SELECT DISTINCT 将表中每个唯一的年-月值作为单个记录提供给我们。然后,我们对该时间点或更早时间点的所有行进行计数,以找到运行总数。