Mysql 统计每日注册人数和累计注册人数

sql 语句如下:

 

SET @i = 0;
SELECT  cc.* ,(@i:=@i+cc.当日注册人数) as 累计注册人数
FROM
  (SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as "日期" , count(*) as "当日注册人数" FROM user_t GROUP BY  日期)cc
  CROSS JOIN (select @i := 0) x;
执行结果如下：

 

mybatis 使用如下：

 

@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserSum {
    private String dateTime;
    private String dayData;
    private String totalNum;
}

 

userMapper：

@Mapper
public interface UserMapper {

    /**
     *  获取列表
     * @return List<Usersum>
     * @decript 获取列表
     */
    List<UserSum> getList();
}

 

    <select id="getList" resultType="com.dxylearn.study.mymybatis.UserSum">
   SELECT cc.*, (@i:=@i+cc.dayData) as totalNum
FROM
  (SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as "dateTime" , count(*) as "dayData" FROM test.`user` GROUP BY dateTime)cc
  CROSS JOIN (select @i := 0) x;
    </select>

结果：