MySQL按日期分组并统计截止当前时间的总数

世间安得双全法,不负如来不负卿
表结构如下所示:users表示当天新增的用户数

现在的需求是这样的:按每天分组,查询当天新增的用户总数和截止到当前时间新增的用户总数,结果如下:

SQL语句如下:

SELECT
    reg_time,
    total1 as '小计',
    @total := @total + total1 AS '总计'
FROM
    (
        SELECT
            date(reg_time) AS reg_time,
            sum(users) AS total1
        FROM
            test
        GROUP BY
            date(reg_time)
    ) AS temp,
    (SELECT @total := 0) AS T1
ORDER BY
    reg_time;

解释一下:SELECT @total := 0,,这句的意思是给临时变量@total设置值为0;

如此即可得出上面的结果满足需求.