如何用SQL查询两个日期间存在空余可用工时的团队

场景说明 #

需要统计指定时间区间内各团队的空闲时段，用于工作任务分配，涉及两张业务表：

表1：Working_Dates（排班工时表） #

表2：Teams（团队信息表） #

查询目标区间 #

20.08.2021 08:00 至 20.08.2021 18:00

预期输出 #

实现逻辑 #

1. 先过滤掉查询区间内完全被排班覆盖的团队：只要存在排班记录的开始时间早等于查询开始、结束时间晚等于查询结束，该团队直接排除，比如示例中的B1团队
2. 剩余团队分两种情况计算空闲时段：
   • 团队在查询区间内无任何重叠排班：空闲时段为整个查询区间
   • 团队在查询区间内有部分重叠排班：空闲时段为查询开始时间到排班开始时间

参考SQL实现（MySQL版本） #

-- 定义查询区间参数
SET @QUERY_START = STR_TO_DATE('20.08.2021 08:00', '%d.%m.%Y %H:%i');
SET @QUERY_END = STR_TO_DATE('20.08.2021 18:00', '%d.%m.%Y %H:%i');

SELECT
  t.TEAM_NO,
  DATE_FORMAT(@QUERY_START, '%d.%m.%Y %H:%i') AS FREE_DATETIME_START,
  DATE_FORMAT(IFNULL(w.START_DATE, @QUERY_END), '%d.%m.%Y %H:%i') AS FREE_DATETIME_END
FROM Teams t
LEFT JOIN Working_Dates w
  ON t.TEAM_NO = w.TEAM_NO
  -- 匹配和查询区间有重叠的排班记录
  AND w.START_DATE < @QUERY_END
  AND w.END_DATE > @QUERY_START
WHERE
  -- 排除整个查询区间都被排班占满的团队
  NOT EXISTS (
    SELECT 1 FROM Working_Dates w2
    WHERE w2.TEAM_NO = t.TEAM_NO
    AND w2.START_DATE <= @QUERY_START
    AND w2.END_DATE >= @QUERY_END
  )
GROUP BY t.TEAM_NO, w.START_DATE
ORDER BY t.TEAM_NO;

适配说明 #

• 如果你使用的是Oracle、SQL Server等其他数据库，仅需要修改日期格式化、日期转换函数即可，核心逻辑通用
• 如果需要支持一个团队在查询区间内有多段排班的多间隙空闲计算，可以在此基础上扩展窗口函数排序相邻排班的逻辑

内容的提问来源于stack exchange，提问作者Mehmet Ali DUMLU