MySQL按日期依次統計

• ​​解決方案​​
• ​​講解​​

• ​​1. 準備資料​​
• ​​2.GROUP BY 方式​​
• ​​3. 借助中間表累加​​

需求: 按日期依次統計, 如果資料庫沒有的日期也要統計為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. 借助中間表累加