SELECT * FROM test_table
WHERE
? ? (start_time >= a AND start_time <= b)
? ? OR (start_time <= a AND end_time >= b)
? ? OR (end_time >= a AND end_time <= b)?

SELECT * FROM test_table
WHERE
? ? NOT (
? ? ? ? (end_time < a
? ? ? ? OR (start_time > b)
? ? )?

drop table if EXISTS `test_date`;
CREATE TABLE `test_date` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `room_id` int(11) DEFAULT NULL COMMENT '房間id',
  `start_time` datetime DEFAULT NULL COMMENT '房態(tài)開始時(shí)間',
  `end_time` datetime DEFAULT NULL COMMENT '房態(tài)結(jié)束時(shí)間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `test`.`test_date`(`id`, `room_id`, `start_time`, `end_time`) 
VALUES (1, 1, '2020-05-20 15:00:00', '2020-05-22 12:00:00');
INSERT INTO `test`.`test_date`(`id`, `room_id`, `start_time`, `end_time`) 
VALUES (2, 1, '2020-05-25 15:00:00', '2020-05-27 12:00:00');
-- 查詢?nèi)胱r(shí)間
set @s = '2020-05-18 23:59:59';
-- 查詢離店時(shí)間
set @e = '2020-05-21 00:00:00';
-- 這個(gè)sql查詢出來的話，是不合適的數(shù)據(jù)，有重疊
select * from test_date
where 
(start_time <= @s and end_time >= @s)
or
(start_time <= @e and end_time >= @e)
or
(start_time >= @s and end_time <= @e)
or
(start_time <= @s and end_time >= @e);
-- 這個(gè)sql不加反，求出來的是合適的數(shù)據(jù)，不重疊的數(shù)據(jù)
-- 取反，就是重疊的數(shù)據(jù),日期不符合規(guī)范的數(shù)據(jù)，有相交的數(shù)據(jù)
select * from test_date
where
!(start_time >= @e or end_time <= @s);