累加计算

在mysql中实现如上数据累加效果，可使用 set，定义用户变量。

sql 语句：

SET @x = 0;
SELECT
  t1.months,
  t1.register_num ,
  @x := @x+t1.register_num AS total
FROM
  (
   SELECT
      DATE_FORMAT(createTime, "%Y%m") months,
      count(id) AS register_num
   FROM
      t_user
   GROUP BY
      months asc
  ) t1

案例

create table userlog2
(
    id           int,
    name         varchar(10),
    EmailAddress varchar(50),
    lastlogon    varchar(50)
);
insert into userlog2
values (100, 'test4', 'test4@yahoo.cn', '2007-11-25 16:31:26'),
       (13, 'test1', 'test1@yahoo.cn', '2007-3-22 16:27:07'),
       (19, 'test1', 'test1@yahoo.cn', '2007-10-25 14:13:46'),
       (42, 'test1', 'test1@yahoo.cn', '2007-10-25 14:20:10'),
       (45, 'test2', 'test2@yahoo.cn', '2007-4-25 14:17:39'),
       (49, 'test2', 'test2@yahoo.cn', '2007-5-25 14:22:36');

生成一张临时表，表名自定，四列数据，分别是：Name, Last Logor, Num logontime（要求按时间给出每个人的登录次数，登录时间最早的为1，之后分别为2,3,4等）， Num logonday（要求：按天给出每个人的登录次数，同一天多次登录认为是同一次，最早的一次标记为1，之后的依次类推）

DROP TABLE IF EXISTS tmp_table;
CREATE TEMPORARY TABLE tmp_table
select user2.name  as Name,
       user2.time1 as lastlogon,
       user2.rank1 as num_logontime,
       user2.rank2 as num_logonday
from (select *,

             @rank := if(@nam = user1.name, @rank + 1, 1)                                 rank1,
             @rank1 := if(@nam = user1.name, if(@da = user1.day1, @rank1, @rank1 + 1), 1) rank2,
             @nam := user1.name,
             @da := user1.day1

      from (select name,
                   EmailAddress,
                   date_format(lastlogon, '%Y-%m-%d %H:%i:%s')                 as time1,
                   date_format(substring_index(lastlogon, ' ', 1), '%Y-%m-%d') as day1
            from userlog2
            order by name asc, time1 asc
           ) as user1,
           (select @rank := 0
                 , @rank1 := 0
                 , @nam := null
                 , @da := null) temp
      order by user1.name, user1.time1) as user2
;
select *
from tmp_table