裴大头-秦可爱

裴大头-秦可爱

mysql-按天/月统计数据,没有的填充为0

发表于 2021-03-30
裴大头
阅读量 277
更新于 2021-05-26

按天统计

1、创建基础表

CREATE TABLE num (i INT);
INSERT INTO num (i) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(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');
sql

2、查询时间范围

SELECT
	adddate('2018-03-01', numlist.id) AS 'date'
	FROM
	(
	SELECT
	n1.i + n10.i * 10 + n100.i * 100 AS id
	FROM
	num n1
	CROSS JOIN num AS n10
	CROSS JOIN num AS n100
	) AS numlist
	WHERE
	adddate('2018-03-01', numlist.id) <= date_add('2018-03-01',interval 1 month)
sql

3、需要统计的数据

SELECT left(sign_date,10)as udate,count(sign_date) unmber FROM user GROUP BY udate

4、 将上面两条语句一起查询(无值时补零)

SELECT temp.date,
	coalesce(u.unmber,0) 'number' from(
	SELECT
	adddate('2018-03-01', numlist.id) AS 'date'
	FROM
	(
	SELECT
	n1.i + n10.i * 10 + n100.i * 100 AS id
	FROM
	num n1
	CROSS JOIN num AS n10
	CROSS JOIN num AS n100
	) AS numlist
	WHERE
	adddate('2018-03-01', numlist.id) <= date_add('2018-03-01',interval 1 month)) temp
LEFT JOIN 
(SELECT left(sign_date,10)as udate,count(sign_date) unmber FROM user GROUP BY udate) u on temp.date = u.udate order by temp.date
sql

查看结果集

image.png

按月统计差不多,上面的懂了,下面的是一样的

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
sql

查看结果 image.png

评论
来发一针见血的评论吧!
表情
  • 王路飞

    2021-04-29 16:56

    做的不错

    0
    回复
    • 裴大头

      男博主

      2021-04-29 17:10

      @王路飞谢谢

      0
      回复
    • 共1条回复,点击查看
推荐文章
  • Vue项目代码规范

    1点赞1评论

  • JavaScript 的事件循环机制

    1点赞1评论

  • 聊一聊我的文本编辑器

    1点赞11评论

  • JavaScript的常用遍历方法整理

    1点赞8评论

  • 前端面试精选-基础篇

    1点赞0评论

Crafted with by Pei你看雪

小破站居然运行了 865 天访客 23354

© 2023 Pei你看雪鲁ICP备19037910号-2