【SQL练习】Oracle时间交叉SQL查询方法

在 Oracle 中处理时间交叉（判断两个时间段是否有重叠）是一个非常常见的需求，例如会议室预订、排班、促销活动时间冲突等。

核心思路是：两个时间段 [开始时间A, 结束时间A] 和 [开始时间B, 结束时间B] 不重叠的条件只有一个，那就是时间段A完全在时间段B之前或之后。 取其否命题，就是重叠的条件。

核心判断逻辑

两个时间段重叠的 SQL 条件可以写为：

-- A 的结束时间在 B 的开始时间之后，并且 A 的开始时间在 B 的结束时间之前
A.Start_Time < B.End_Time
AND
A.End_Time > B.Start_Time

这个条件涵盖了所有重叠的情况，包括：

1. A 包含 B
2. B 包含 A
3. A 和 B 部分交叉
4. A 和 B 首尾相接（如果你的业务定义相接不算重叠，则需要调整，通常用 >= 或 <=）

---

常见应用场景与 SQL 写法

假设我们有一张 reservations（预订表），结构如下：

字段名	类型	描述
id	NUMBER	主键
resource_id	NUMBER	资源ID（如会议室ID）
start_time	TIMESTAMP	开始时间
end_time	TIMESTAMP	结束时间

场景 1：插入新预订时，检查是否与已有预订冲突

我们想插入一条新的预订记录 (new_resource_id, new_start_time, new_end_time)，需要检查相同资源上是否存在时间冲突。

SELECT COUNT(*)
INTO v_count -- 如果你在PL/SQL中
FROM reservations r
WHERE r.resource_id = new_resource_id
  AND r.start_time < new_end_time
  AND r.end_time > new_start_time;

-- 如果 v_count > 0，则表示存在时间冲突

直接用于插入前检查的 SQL：

SELECT COUNT(*)
FROM reservations
WHERE resource_id = :new_resource_id
  AND (
      -- 核心交叉判断
      (:new_start_time < end_time AND :new_end_time > start_time)
      -- 或者你想排除自己（更新操作时有用），可以加上 AND id != :current_id
  );

如果这个查询返回任何行（COUNT(*) > 0），就意味着有冲突，你应该阻止插入。

场景 2：查询某个资源在给定时间段内的所有冲突预订

SELECT *
FROM reservations r
WHERE r.resource_id = :input_resource_id
  AND r.start_time < :input_end_time
  AND r.end_time > :input_start_time;
-- 如果要排除某条记录本身（例如在更新操作时），加上： AND r.id != :exclude_id

场景 3：查询所有存在时间交叉的预订记录（自关联）

这个查询用于找出系统中所有存在冲突的预订对，通常用于数据校验。

SELECT
    a.id AS a_id,
    a.resource_id AS a_resource,
    a.start_time AS a_start,
    a.end_time AS a_end,
    b.id AS b_id,
    b.resource_id AS b_resource,
    b.start_time AS b_start,
    b.end_time AS b_end
FROM reservations a
JOIN reservations b
    ON a.resource_id = b.resource_id -- 通常只检查同一资源上的冲突
    AND a.id < b.id -- 防止重复配对 (A-B 和 B-A 是相同的)，只显示一次
    AND a.start_time < b.end_time
    AND a.end_time > b.start_time;
ORDER BY a.resource_id, a.start_time;

---

重要注意事项

1. 时间精度问题：

   • 如果你的字段是 DATE 类型，它包含年月日时分秒。但如果业务只关心日期（如酒店预订），DATE 类型的 00:00:00 可能会带来麻烦。
   • 例如：预订 1：止于 2023-10-01 00:00:00；预订 2：始于 2023-10-01 00:00:00。用 > 和 < 判断，它们不交叉。这通常符合逻辑（前者在9月30日结束，后者在10月1日开始）。
   • 如果需要包含边界，请使用 >= 和 <=。请务必根据你的业务逻辑明确边界条件。

2. 索引：

   • 为了这类查询的性能，尤其是在大表上，强烈建议建立复合索引。
   • 最佳的索引通常是 (resource_id, start_time, end_time)。这样数据库可以快速定位到特定资源，并基于开始和结束时间进行筛选。

3. NULL 值：

   • 如果你的 start_time 或 end_time 允许为 NULL，你需要考虑 NULL 的含义（例如“未结束”）。判断逻辑需要调整，可能要用 NVL 或 COALESCE 函数。
   • 例如，如果一个会议的 end_time 是 NULL（表示正在进行），那么判断它是否与未来时间 :new_start_time 冲突的条件可以改为：

     AND (r.end_time IS NULL OR r.end_time > :new_start_time)
     -- 意思是：如果它还没结束，或者它计划结束的时间在新开始时间之后，就算冲突
     

总结

记住核心的不重叠条件，然后取反：
不重叠：A.End_Time <= B.Start_Time OR A.Start_Time >= B.End_Time
重叠（冲突）：NOT (不重叠) => A.End_Time > B.Start_Time AND A.Start_Time < B.End_Time

根据你的具体业务场景，将这个核心逻辑套用到 WHERE 子句中，并注意处理边界精度和索引优化即可。