MySQL按日期依次统计

MySQL按日期依次统计

需求: 按日期依次统计, 如果数据库没有的日期也要统计为0
实现效果 日期连续并且没有数据统计为0:

解决方案

这里借助了​​mysql.time_zone​​​ 表 需要查看里面有没有数据
如果没有数据请下载到本地然后执行即可:
​​​码云地址​​​

关键sql

SELECT
  dts.df,
  -- 1.注意这里判空
  if(num is null , 0, num) num
FROM
  (
  SELECT
  -- 2.这里开始时间需要动态传过来  DAY是按天(也可以是YEAR MONTH) 
    date_format( date_add( '2021-07-20 00:00:00', INTERVAL + t.Time_zone_id DAY ), '%Y-%m-%d' ) AS 'df' 
  FROM
    mysql.time_zone t 
  WHERE
  -- 3.这里是取多少条数据
    t.Time_zone_id <= '10'
  ) AS dts
  LEFT JOIN (
  -- 4.这里就是你的业务表
    SELECT
      DATE_FORMAT( t.create_time, '%Y-%m-%d' ) dfc,
      count( 0 ) num 
    FROM
      test t
    -- WHERE  可以添加筛选条件
      
    GROUP BY
      dfc 
  ) AS ps 
  ON ps.dfc = dts.df 
ORDER BY
  dts.df

---

讲解

1. 准备数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `create_time` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES (1, '1', '2021-07-29 16:32:46');
INSERT INTO `test` VALUES (2, '2', '2021-07-28 16:33:04');
INSERT INTO `test` VALUES (3, '1', '2021-07-28 16:33:13');
INSERT INTO `test` VALUES (4, '2', '2021-07-27 16:33:20');
INSERT INTO `test` VALUES (5, '1', '2021-07-26 16:33:27');
INSERT INTO `test` VALUES (6, '1', '2021-07-25 16:33:35');
INSERT INTO `test` VALUES (7, '2', '2021-07-24 16:33:44');

SET FOREIGN_KEY_CHECKS = 1;

2.GROUP BY 方式

SELECT
  DATE_FORMAT( t.create_time, '%Y-%m-%d' ) date,
  count( 0 ) num 
FROM
  test t 
GROUP BY
  DATE_FORMAT( t.create_time, '%Y-%m-%d' )

运行结果如下图:
如果我想统计7天的就不行了

3. 借助中间表累加

如果实在看不懂
除了红框里面的需要修改一下
其他的不要修改

​​2021-07-20 00:00:00​​​ 需要换成你要统计的开始时间
​​​DAY​​​ 按天统计 也可以是​​YEAR​​​ ​​MONTH​​​​'%Y-%m-%d'​​ 日期格式
​​10​​ 你要查询多少条数据
最下面的框就是根据你实际业务来了