mysql 按天分组少天数_MySQL按天分组统计，无数据显示0

问题描述：

按照天数统计每天的总数，如果其中有几天没有数据，那么group by 返回会忽略那几天，不符合报表统计的需求。当天没有数据的话该如何填充0 ？

数据库脚本

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for employee

-- ----------------------------

DROP TABLE IF EXISTS `employee`;

CREATE TABLE `employee` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,

`age` int(11) NULL DEFAULT NULL,

`sex` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,

`addr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,

`create_time` datetime(0) NULL DEFAULT NULL,

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of employee

-- ----------------------------

INSERT INTO `employee` VALUES (1, 'zhansan', 18, '男', '北京市', '2019-06-13 18:41:34');

INSERT INTO `employee` VALUES (2, 'lisi', 20, '男', '天津市', '2019-06-14 18:41:43');

INSERT INTO `employee` VALUES (3, 'wangwu', 28, '男', '上海', '2019-06-18 18:41:37');

INSERT INTO `employee` VALUES (4, 'lisi', 35, '男', '香港', '2019-06-18 18:41:51');

INSERT INTO `employee` VALUES (5, 'limei', 22, '女', '山西', '2019-06-19 18:41:58');

INSERT INTO `employee` VALUES (6, 'liting', 26, '女', '北京市', '2019-06-21 18:42:03');

INSERT INTO `employee` VALUES (7, 'wangdan', 31, '女', '河北省', '2019-06-19 19:22:31');

INSERT INTO `employee` VALUES (8, 'limeili', 19, '女', '安徽省', '2019-06-19 19:27:45');

SET FOREIGN_KEY_CHECKS = 1;

先看解决方案：

/**

如果使用当前日期的话 ，将'2019-06-22'替换为CURDATE( )

*/

SELECT

t1.`day`,

COUNT( t2.id ) num

FROM

(

SELECT

@cdate := DATE_ADD( @cdate, INTERVAL - 1 DAY ) DAY

FROM

( SELECT @cdate := DATE_ADD( '2019-06-22', INTERVAL + 1 DAY ) FROM employee ) t0

LIMIT 7

) t1

LEFT JOIN (

SELECT

DATE( a.create_time ) DAY,

a.id

FROM

employee a

WHERE

DATE( create_time ) <= '2019-06-22' AND DATE( create_time ) > DATE_SUB( '2019-06-22', INTERVAL 7 DAY )

) t2 ON t2.DAY = t1.DAY

GROUP BY

t1.`day`;

执行数据结果完全符合需求；

连续日期mysql

/**

如果指定开始日期的话 ，将CURDATE( )替换为'2019-06-13'

*/

SELECT

@cdate := DATE_ADD( @cdate, INTERVAL - 1 DAY ) DAY

FROM

( SELECT @cdate := DATE_ADD( CURDATE( ), INTERVAL + 1 DAY ) FROM employee ) t0

LIMIT 7;

SQL分析：

1. @cdate := 是定义名为cdate的变量并赋值(select 后面必须用:=)

2. @cdate := DATE_ADD(CURDATE(), INTERVAL + 1 DAY) 按照当前日期，加一天

3. SELECT @cdate := DATE_ADD(CURDATE(), INTERVAL + 1 DAY) FROM 数据库表名

4. @cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY 把定义的cdate变量天数-1(自减)

5. LIMIT 7 限制一下条数，得到了指定日期往前7天的记录

6. left join group by t1.day 即按照左表关联业务数据，根据左表的日期分组，即分成了指定的7天数据，有记录就统计条数，没有记录就是0