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

MySQL按日期分组并统计截止当前时间的总数 建表语句 SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_reg -- ---------------------------- DROP TABLE IF EXISTS `t_reg`;

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

建表语句

SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------ Table structure for t_reg-- ----------------------------DROP TABLE IF EXISTS `t_reg`;CREATE TABLE `t_reg`  (  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID编号',  `REG_TIME` datetime(0) NULL DEFAULT NULL COMMENT '时间',  `REG_COUNT` int(11) NULL DEFAULT NULL COMMENT '数量',  PRIMARY KEY (`ID`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ------------------------------ Records of t_reg-- ----------------------------INSERT INTO `t_reg` VALUES (1, '2019-05-01 08:08:12', 1);INSERT INTO `t_reg` VALUES (2, '2019-05-02 00:09:09', 10);INSERT INTO `t_reg` VALUES (3, '2019-05-02 13:08:08', 2);INSERT INTO `t_reg` VALUES (4, '2019-05-03 15:08:05', 4);INSERT INTO `t_reg` VALUES (5, '2019-05-03 17:08:08', 6);INSERT INTO `t_reg` VALUES (6, '2019-05-16 21:01:12', 4);INSERT INTO `t_reg` VALUES (7, '2019-05-03 05:08:09', 11);

SET FOREIGN_KEY_CHECKS = 1;

表结构如下所示：REG_COUNT  表示当天新增的用户数

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

SQL语句如下:

SELECT    reg_time,    min_total AS '小计',    @total := @total + min_total AS '总计' FROM    ( SELECT date( reg_time ) AS reg_time,     sum( reg_count ) AS min_total FROM t_reg GROUP BY date( reg_time ) ) AS temp,    ( SELECT @total := 0 ) AS T1 ORDER BY    reg_time;

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

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