如何在SQL查询中排除存在任务日期重叠的员工分配候选记录

实现逻辑 #

两个时间段存在重叠的核心判定规则：

• 待分配任务的起始时间 < 已分配任务的结束时间
• 且待分配任务的结束时间 > 已分配任务的起始时间
  只要候选员工对应存在任意符合上述条件的已分配任务，就将该员工从对应任务的候选列表中排除。

参考SQL实现（NOT EXISTS写法，语义最清晰，执行效率也更高） #

SELECT 
    c.task_id,
    c.skill_id,
    c.employee_id
FROM 
    候选员工表 c
WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM 已分配任务表 a
        WHERE 
            a.employee_id = c.employee_id
            AND c.task_start < a.assign_end
            AND c.task_end > a.assign_start
    );

注：上述时间判断逻辑基于「任务结束当日和另一任务开始当日为同一天不算重叠」的通用规则，如果你的业务要求同一天就算重叠，将<改为<=、>改为>=即可。

按照你提供的示例数据，执行上述SQL后输出结果为：

可选实现（LEFT JOIN写法） #

如果习惯用关联写法，可以用左连接后过滤空值的方式实现，效果完全一致：

SELECT 
    DISTINCT c.task_id,
    c.skill_id,
    c.employee_id
FROM 
    候选员工表 c
LEFT JOIN 已分配任务表 a 
    ON a.employee_id = c.employee_id
    AND c.task_start < a.assign_end
    AND c.task_end > a.assign_start
WHERE 
    a.assign_id IS NULL;

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