当前位置：首页 > article >正文

sql 时间交集

article 2024/10/1 12:27:35

任务（取时间交集）

前端输入开始时间和结束时间，通过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;