SQL Server中如何基于时间可用性查询符合条件的空闲医生

需求梳理 #

现有Schedule表存储医生已预约日程，字段StartTime、EndTime为TIME(0)类型，医生每周总可排班时长固定为40小时，需要筛选出**每周可用时长占总时长比例≥50%**的医生ID。

核心逻辑 #

可用时长占比≥50%等价于：已预约总时长 ≤ 20小时（40小时 * 50%），所以只需要按医生分组统计所有已预约时长总和，过滤符合条件的分组即可。

实现代码 #

基础版（仅统计有预约记录的医生，适配MySQL） #

SELECT DoctorId
FROM Schedule
GROUP BY DoctorId
-- 40小时总时长减去已预约总时长 ≥ 20小时即可满足占比要求
HAVING 40 - (SUM(TIME_TO_SEC(TIMEDIFF(EndTime, StartTime))) / 3600) >= 20;

增强版（包含无任何预约记录的医生，适配MySQL） #

如果存在独立的Doctors医生基础表，需要包含还没有任何预约（可用时长100%）的医生，可使用左关联实现：

SELECT d.DoctorId
FROM Doctors d
LEFT JOIN Schedule s ON d.DoctorId = s.DoctorId
GROUP BY d.DoctorId
-- 无预约记录的医生已预约时长默认为0
HAVING 40 - IFNULL(SUM(TIME_TO_SEC(TIMEDIFF(s.EndTime, s.StartTime))) / 3600, 0) >= 20;

其他数据库适配说明 #

仅需要替换时间差计算函数即可：

• SQL Server：SUM(DATEDIFF(second, StartTime, EndTime)) / 3600.0
• PostgreSQL：SUM(EXTRACT(EPOCH FROM (EndTime - StartTime)) / 3600)

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