mysql统计每年每月每天数据

这里我们使用mysql的内置函数 YEAR 、MONTH 、DAY 三个函数以及GROP BY 进行分组查询，其中前面的三个函数是根据传入的日期返回对应的年、月、日。

表信息如下：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for info
-- ----------------------------
DROP TABLE IF EXISTS `info`;
CREATE TABLE `info`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `info` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `date` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of info
-- ----------------------------
INSERT INTO `info` VALUES (1, '小猪', '11111', '2023-11-14 16:14:57');
INSERT INTO `info` VALUES (2, '小妞', '22222', '2023-11-14 16:15:13');
INSERT INTO `info` VALUES (3, '小狗', '333333', '2023-11-13 16:15:32');
INSERT INTO `info` VALUES (4, '小姨', 'dsdsds', '2023-11-12 16:15:47');
INSERT INTO `info` VALUES (5, 'asa', 'dsdsds', '2023-10-01 16:16:03');
INSERT INTO `info` VALUES (6, 'gbgfbf', 'bgfbfgbfg', '2023-10-12 16:16:12');
INSERT INTO `info` VALUES (7, 'gfhbfth', 'gbtfhfht', '2023-10-03 16:16:23');
INSERT INTO `info` VALUES (8, 'yewa', 'cdscdscs', '2022-12-01 16:22:13');
INSERT INTO `info` VALUES (9, 'cdscsdc', 'cdscsdc', '2022-11-01 16:22:27');

SET FOREIGN_KEY_CHECKS = 1;

查询语句：

SELECT  YEAR(date) as year,month(date) as month,DAY(date) as day,COUNT(id) as amount   
FROM info
GROUP BY YEAR(date) ,MONTH(date),DAY(date)

结果：