Mybatis：查询开始时间与结束时间在指定的日期范围之内，并且结束时间可以为NULL的数据

Monly21

于 2024-08-19 21:54:11 发布

阅读量418

点赞数 3

分类专栏： MySQL 文章标签： mybatis android

本文链接：https://blog.csdn.net/jhgfvjfggff_fds_f/article/details/141335786

版权

MySQL 专栏收录该内容

24 篇文章 1 订阅

订阅专栏

数据表创建

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80032
 Source Host           : localhost:3306
 Source Schema         : mysql_demo

 Target Server Type    : MySQL
 Target Server Version : 80032
 File Encoding         : 65001

 Date: 19/08/2024 21:21:12
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  `age` int NULL DEFAULT NULL COMMENT '年龄',
  `gender` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '0' COMMENT '性别',
  `kssj` datetime NULL DEFAULT NULL COMMENT '开始时间',
  `jssj` datetime NULL DEFAULT NULL COMMENT '结束时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES (1, '张三', 12, '0', '2024-08-18 21:19:07', '2024-08-19 21:19:51');
INSERT INTO `tb_user` VALUES (2, '李四', 34, '0', '2024-08-14 21:20:09', '2024-08-15 21:20:13');
INSERT INTO `tb_user` VALUES (3, '王五', 32, '0', '2024-08-09 21:20:27', NULL);
INSERT INTO `tb_user` VALUES (4, '赵六', 77, '0', '2024-08-10 21:20:59', NULL);

SET FOREIGN_KEY_CHECKS = 1;

数据查询

这是查询的是数据日期在2024-08-01至2024-08-15范围内，并且结束时间可以为NULL的数据

SELECT
	* 
FROM
	`tb_user` 
WHERE
	kssj BETWEEN '2024-08-01 00:00:00' AND '2024-08-15 23:59:59' 
	AND (jssj BETWEEN '2024-08-01 00:00:00' AND '2024-08-15 23:59:59' OR jssj IS NULL);