sql 时间交集

任务（取时间交集）

前端输入开始时间和结束时间，通过sql筛选出活动开始时间和活动结束时间再开时时间和结束时间有交集的活动

想法：

前后一段时间内遇到了类似取交集的，从网上找到了两种写法，再结合GPT等工具比对了，记录该文章，若有错误请指正。

代码

-- 原始查询
SELECT *
FROM activities
WHERE 
    (activity_start_time <= :input_end_time AND activity_end_time >= :input_start_time)
    OR
    (activity_start_time >= :input_start_time AND activity_start_time <= :input_end_time)
    OR
    (activity_end_time >= :input_start_time AND activity_end_time <= :input_end_time)

-- 简化查询
SELECT *
FROM activities
WHERE activity_start_time <= :end_input
  AND activity_end_time >= :start_input;

● 原始查询： 这个查询语句比较复杂，它通过三个条件的组合来筛选数据。这三个条件分别表示：
○ 活动的开始时间在输入的结束时间之前，并且活动的结束时间在输入的开始时间之后。
○ 活动的开始时间在输入的开始时间之后，并且活动的开始时间在输入的结束时间之前。
○ 活动的结束时间在输入的开始时间之后，并且活动的结束时间在输入的结束时间之前。
● 简化查询： 这个查询语句相对简单，它只用两个条件来筛选数据。这两个条件表示：
○ 活动的开始时间在输入的结束时间之前。
○ 活动的结束时间在输入的开始时间之后。

结论

经过分析，这两个查询语句是等价的。简化后的查询语句通过更简洁的条件表达了与原始查询相同的含义。

其他证明材料

-- 创建测试表
CREATE TABLE IF NOT EXISTS activities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    activity_start_time DATETIME,
    activity_end_time DATETIME
);

-- 清空表
TRUNCATE TABLE activities;

-- 插入测试数据
INSERT INTO activities (name, activity_start_time, activity_end_time) VALUES
    ('Activity 1', '2023-01-01 10:00:00', '2023-01-01 12:00:00'),
    ('Activity 2', '2023-01-01 11:00:00', '2023-01-01 13:00:00'),
    ('Activity 3', '2023-01-01 13:00:00', '2023-01-01 15:00:00'),
    ('Activity 4', '2023-01-01 09:00:00', '2023-01-01 11:30:00'),
    ('Activity 5', '2023-01-01 14:00:00', '2023-01-01 16:00:00');

-- 定义测试案例
SET @test_cases = '
(''2023-01-01 10:30:00'', ''2023-01-01 14:30:00''),
(''2023-01-01 09:00:00'', ''2023-01-01 11:00:00''),
(''2023-01-01 12:00:00'', ''2023-01-01 13:00:00''),
(''2023-01-01 08:00:00'', ''2023-01-01 17:00:00''),
(''2023-01-01 15:30:00'', ''2023-01-01 16:30:00'')
';

-- 创建临时表来存储测试案例
CREATE TEMPORARY TABLE test_cases (
    start_time DATETIME,
    end_time DATETIME
);

-- 将测试案例插入临时表
SET @sql = CONCAT('INSERT INTO test_cases (start_time, end_time) VALUES ', @test_cases);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 执行测试并显示结果
SELECT 
    tc.start_time,
    tc.end_time,
    CASE 
        WHEN (SELECT COUNT(*) FROM activities 
              WHERE (activity_start_time <= tc.end_time AND activity_end_time >= tc.start_time)
                 OR (activity_start_time >= tc.start_time AND activity_start_time <= tc.end_time)
                 OR (activity_end_time >= tc.start_time AND activity_end_time <= tc.end_time)) 
             = 
             (SELECT COUNT(*) FROM activities 
              WHERE activity_start_time <= tc.end_time
                AND activity_end_time >= tc.start_time)
        THEN '等价'
        ELSE '不等价'
    END AS 结果
FROM test_cases tc;

-- 清理
DROP TEMPORARY TABLE test_cases;

这个脚本做了以下几件事：

1. 创建并填充了 activities 表，包含了多个活动的开始和结束时间。
2. 定义了多个测试案例，覆盖了不同的时间范围。
3. 创建了一个临时表来存储这些测试案例。
4. 对每个测试案例，执行两个查询并比较它们的结果。
5. 显示每个测试案例的结果，指明两个查询是否等价。
   测试案例包括：
6. 跨越多个活动的时间范围
7. 仅覆盖一个活动的开始部分
8. 仅覆盖一个活动的结束部分
9. 覆盖所有活动的时间范围
10. 不覆盖任何活动的时间范围