时间戳

首页 > 解决方案 > 我可以只使用 SQL 进行范围检查吗？

sql - 我可以只使用 SQL 进行范围检查吗？

问题描述

我正在为家长/教师会议选择会议时间段。当父母也是老师并且正在选择与另一位老师注册的时间段时，我想排除为父母预订的任何时间段作为老师。例如，高中教师 ParentTeacher Smith 的高中会议从上午 8:00 到下午 3:00 开始，相隔 20 分钟。即 8:00、8:20、8:40、9:00 等。Smith 在中学有一个学生，从上午 8:00 到下午 3:00 开始，那里的会议间隔 15 分钟。即 8:00、8:15、8:30、8:45、9:00 等。现在，Smith 想与 MiddleSchool Jones 预约一个会议。目前史密斯在高中只有一次会议，时间是 8:40-9:00。当史密斯想与琼斯预订时，在 8:30 和 8:45 与琼斯发生冲突。

我可以查询史密斯的日程安排，并有一个包含预定时间段的结果集；8:40-9:00 等以及琼斯的开放时间段的结果集，但我不希望它包括 8:30-8:45 或 8:45-9:00 时间段。我正在考虑的蛮力方法是使用脚本循环遍历琼斯开放时间段，根据史密斯预定的时间段检查每个时间段，而不显示冲突的时间段。这似乎不雅且耗时。是否有一个查询可以让我只选择不与预订时间段重叠的时间段？可能带有预订时间段的子查询？

史密斯（236）被黄牌警告

SELECT intSlotID, dtSlotStart, dtSlotEnd
FROM tblSchedules
WHERE intTeacherID = 236 AND intParentID <> 0 AND intSchoolID = 1

琼斯 (343) 打开

SELECT intSlotID, dtSlotStart, dtSlotEnd
FROM tblSchedules
WHERE intTeacherID = 343 AND intParentID = 0 AND intSchoolID = 2

标签： sql

解决方案

根据我对您的问题的理解，这是一个适用于 Oracle 数据库的解决方案。我相信在您的特定数据库中转换不会花很长时间

让我们创建调度数据库并使用 Jones 和 Smith 槽填充它：

CREATE TABLE schedule (teacher_id number, slot_start number, slot_end number, booked char(1));

INSERT INTO schedule(teacher_id, slot_start, slot_end, booked)
SELECT 343 AS teacher_id, 8 * 60 + (level -1) * 15 AS slot_start, 8 * 60 + level * 15 AS slot_end, 'N' as booked
FROM dual connect by Level < 7 * 4 + 1;

INSERT INTO schedule(teacher_id, slot_start, slot_end, booked)
SELECT 236 AS teacher_id, 8 * 60 + (level -1) * 20 AS slot_start, 8 * 60 + level * 20 AS slot_end, 'N' as booked
FROM dual connect by Level < 7 * 3 + 1;

请注意，我将时段开始和结束时间表示为自午夜以来的分钟数。您可以适应更多的多天调度程序，以获取自年初以来的分钟数或任何其他适合您的业务建模。

现在让我们预订史密斯的 8:40 - 9:00 时段......再次以分钟表示。

update schedule set booked = 'Y' WHERE teacher_id = 236 AND slot_start = 60 * 8 +40;

这是您选择可用琼斯插槽的查询：

SELECT teacher_id,
       trunc(slot_start/60) || ':' || RPAD(to_char(MOD(slot_start, 60)), 2, '0') as slot_start,
       trunc(slot_end/60) || ':' || RPAD(to_char(MOD(slot_end, 60)), 2, '0') as slot_end
 FROM schedule jones
 WHERE teacher_id = 343
   AND jones.booked = 'N'
 AND NOT EXISTS (
   SELECT 1
     FROM schedule smith
    WHERE smith.teacher_id = 236
      AND smith.booked = 'Y'
      AND ((smith.slot_start BETWEEN jones.slot_start AND jones.slot_end)
            OR
            (smith.slot_end BETWEEN jones.slot_start AND jones.slot_end)
          )
 )
ORDER BY jones.slot_start;

我进行了分钟到小时的转换，以使结果更易于阅读：

如您所见，未选择 8:30 到 8:45 和 8:45 到 9:00 时隙。

teacher_id slot_start   slot_end
343         8:00            8:15
343         8:15            8:30
343         9:15            9:30
343         10:15           10:30
.....

现在让我们在 9:40 到 10:00 之间为 Smith 预订另一个时段。并再次运行相同的选择

update schedule set booked = 'Y' WHERE teacher_id = 236 AND slot_start = 60 * 9 +40;

从结果中可以看出， (:30 到 9:45 和 9:45 到 10:00 时隙也被排除在外

推荐阅读