mysql-按天/月统计数据,没有的填充为0
1、创建基础表
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `num`
-- ----------------------------
DROP TABLE IF EXISTS `num`;
CREATE TABLE `num` (
`i` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of num
-- ----------------------------
INSERT INTO `num` VALUES ('0');
INSERT INTO `num` VALUES ('1');
INSERT INTO `num` VALUES ('2');
INSERT INTO `num` VALUES ('3');
INSERT INTO `num` VALUES ('4');
INSERT INTO `num` VALUES ('5');
INSERT INTO `num` VALUES ('6');
INSERT INTO `num` VALUES ('7');
INSERT INTO `num` VALUES ('8');
INSERT INTO `num` VALUES ('9');
-- 测试表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`birthday` date DEFAULT NULL COMMENT '生日',
`sex` char(1) DEFAULT NULL COMMENT '性别',
`address` varchar(256) DEFAULT NULL COMMENT '地址',
`sign_date` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO `user` VALUES ('1', '小南', '1991-04-04', '男', '湖南湘潭', '2018-03-13');
INSERT INTO `user` VALUES ('2', '小谭', '1981-01-27', '男', '湖南湘潭', '2018-03-12');
INSERT INTO `user` VALUES ('3', '小梅', '1981-04-28', '女', '广东梅县', '2018-04-14');
INSERT INTO `user` VALUES ('4', '小仪', '1986-04-12', '女', '四川仪陇', '2018-03-13');
INSERT INTO `user` VALUES ('5', '小蓝', '2001-01-01', '女', '上海嘉定', '2018-03-15');
2、查询时间范围
SELECT adddate('2019-01-01', interval numlist.id month) AS 'date' FROM
(SELECT * from
(SELECT n1.i + n10.i * 10 AS id FROM num n1 CROSS JOIN num AS n10) a2 ) AS numlist
WHERE adddate('2019-01-01', interval numlist.id month) <= '2019-12-01'
3、需要统计的数据
SELECT left(sign_date,7)as udate,count(sign_date) unmber FROM user GROUP BY udate
4、 将上面两条语句一起合并查询(无值时或null补零)
SELECT left(temp.date,7),
coalesce(u.unmber,0) 'number' from(
SELECT adddate('2018-01-01', interval numlist.id month) AS 'date' FROM
(
SELECT * from
(SELECT n1.i + n10.i * 10 AS id FROM num n1 CROSS JOIN num AS n10) a
where a.id<=11
) AS numlist
WHERE adddate('2018-01-01', interval numlist.id month) <= '2018-12-01') temp
LEFT JOIN
(SELECT left(sign_date,7)as udate,count(sign_date) unmber FROM user GROUP BY udate) u on left(temp.date,7) = u.udate ORDER BY temp.date
5、结果