MyBatis：按照日期分组查询

基础查询

查询指定数据日期的数据

SELECT DATE_FORMAT(date_add('2024-08-01', INTERVAL ( help_topic_id ) DAY ), '%Y-%m-%d' ) dt FROM mysql.help_topic WHERE help_topic_id < timestampdiff(DAY,'2024-08-01', concat(DATE_ADD('2024-08-19', INTERVAL 1 DAY )))

实际应用

根据日期分组查询学生的考试成绩

创建数据表

DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int NULL DEFAULT NULL,
  `sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `score` decimal(10, 2) NULL DEFAULT NULL,
  `rq` date NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `tb_user` VALUES (1, '张三', 12, '0', 52.00, '2024-08-01');
INSERT INTO `tb_user` VALUES (2, '张三', 12, '0', 68.00, '2024-08-07');
INSERT INTO `tb_user` VALUES (3, '张三', 12, '0', 62.00, '2024-08-17');

数据分组查询

SELECT * FROM (SELECT DATE_FORMAT(date_add('2024-08-01', INTERVAL ( help_topic_id ) DAY ), '%Y-%m-%d' ) dt FROM mysql.help_topic WHERE help_topic_id < timestampdiff(DAY,'2024-08-01', concat(DATE_ADD('2024-08-19', INTERVAL 1 DAY )))) rqfw LEFT JOIN tb_user tbuser ON rqfw.dt=tbuser.rq