如何查询真正重叠的日期记录（排除首尾衔接场景）

需求说明 #

从业务表中筛选出所有存在日期重叠的记录，但需排除以下场景：A记录的check_out与B记录的check_in完全相等的首尾衔接情况。

测试表结构与数据 #

-- 创建临时表
CREATE TABLE `abc` (
`id` int(11) NOT NULL,
`propertie_id` int(11) NOT NULL,
`rooms_id` int(11) NOT NULL,
`block_name` varchar(256) NOT NULL,
`check_in` date NOT NULL,
`check_out` date NOT NULL,
`status` tinyint(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入测试数据
INSERT INTO `abc` (`id`, `propertie_id`, `rooms_id`, `block_name`, `check_in`, `check_out`, `status`) VALUES
(1, 33, 5, 'BeachHouse', '2022-09-01', '2022-09-04', 3),
(2, 33, 5, 'BeachHouse', '2022-09-04', '2022-09-06', 2),
(3, 33, 5, 'BeachHouse', '2022-11-28', '2022-12-04', 1),
(4, 33, 5, 'BeachHouse', '2022-12-04', '2022-12-14', 13),
(5, 44, 6, 'C3', '2022-09-24', '2022-09-26', 2),
(6, 44, 16, 'C3', '2022-09-26', '2022-09-29', 13),
(7, 46, 12, '8BR', '2022-12-26', '2023-01-11', 2),
(8, 46, 12, '8BR', '2023-01-01', '2023-01-11', 1),
(9, 47, 4, 'Skye', '2022-12-21', '2023-01-01', 3),
(10, 47, 4, 'Skye', '2023-01-01', '2023-01-03', 1);

原SQL问题分析 #

原SQL会将首尾衔接的记录误纳入结果，因为日期判断条件o2.check_in <= o.check_out AND o.check_in <= o2.check_out包含了check_out与check_in完全相等的场景。

原SQL代码：

SELECT o.propertie_id,o.rooms_id,o.block_name,o.check_in,o.check_out,o.status 
FROM vi_bookings o
WHERE EXISTS
(
 SELECT 1 FROM vi_bookings o2
 WHERE o2.`propertie_id` = o.`propertie_id`
 AND o2.`rooms_id` = o.`rooms_id`
 AND o2.`block_name` = o.`block_name`
 AND o2.check_in <= o.check_out 
 AND o.check_in <= o2.check_out 
 AND o2.id != o.id
)
AND o.status NOT IN (4,10)
ORDER BY o.`propertie_id`, o.`block_name`, o.check_in

优化后的SQL #

将日期重叠判断条件中的<=改为<，即可排除首尾衔接场景，仅保留真正的日期重叠记录：

SELECT o.propertie_id,o.rooms_id,o.block_name,o.check_in,o.check_out,o.status 
FROM vi_bookings o
WHERE EXISTS
(
 SELECT 1 FROM vi_bookings o2
 WHERE o2.`propertie_id` = o.`propertie_id`
 AND o2.`rooms_id` = o.`rooms_id`
 AND o2.`block_name` = o.`block_name`
 -- 修改条件排除首尾衔接
 AND o2.check_in < o.check_out 
 AND o.check_in < o2.check_out 
 AND o2.id != o.id
)
AND o.status NOT IN (4,10)
ORDER BY o.`propertie_id`, o.`block_name`, o.check_in

结果说明 #

优化后的SQL仅返回测试数据中真正重叠的记录（即id=7和id=8的两条记录），像id=1与id=2、id=3与id=4这类首尾衔接的记录会被排除。

内容的提问来源于stack exchange，提问作者Hola