SQL查询指定起止时间范围内关联班级的管理人员实现方案

核心逻辑说明 #

我们要找的是和查询参数[param_start_time, param_end_time]存在时间重叠的班级管理记录，首先明确时间重叠判断规则：

• 两条时间区间存在重叠的通用判断：区间1开始 < 区间2结束 AND 区间1结束 > 区间2开始
• 表中end_time为NULL代表该班级的管理关系至今有效，判断时等价于无穷大的时间戳，必然大于任意查询的param_end_time

正确查询语句 #

SELECT 
    class_id,
    mc.start_time,
    mc.end_time 
FROM manager_class AS mc
WHERE 
    mc.manager_id = 999
    -- 时间重叠判断，兼容end_time为NULL的场景
    AND mc.start_time < param_end_time
    AND COALESCE(mc.end_time, '9999-12-31 23:59:59'::timestamp(3)) > param_start_time

注：你也可以用mc.end_time IS NULL OR mc.end_time > param_start_time替换COALESCE部分，逻辑完全等价：

AND (mc.end_time IS NULL OR mc.end_time > param_start_time)

用例验证 #

上述语句可以满足你列出的所有查询用例：

1. 参数为2021-05-05 17:30:48到2021-05-05 17:42:43时，匹配class_id 1、2
2. 参数为2021-05-05 17:39:38到2021-05-05 17:42:43时，匹配class_id 2
3. 参数为2021-05-05 17:39:38到2021-05-05 17:55:43时，匹配class_id 2、3、4
4. 参数为2021-05-05 17:47:45到2021-05-05 17:47:46时，匹配class_id 4
5. 参数为2021-05-05 17:39:38到2021-05-05 17:47:45时，匹配class_id 2、3、4
6. 参数为2021-05-05 17:39:38到2021-05-05 17:40:00时，匹配class_id 2

原SQL的问题 #

你之前写的SQL只覆盖了部分重叠场景：比如班级时间完全包含查询时间、查询时间完全包含班级时间的情况，漏掉了班级结束时间落在查询区间内、班级开始时间落在查询区间外但结束时间在查询区间内的场景，所以会出现查询结果不全的问题。

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