mysql - SQL 在可用性之外转移

Question

我正在尝试将一个 sql 查询放在一起，以获取超出日程安排应用程序可用性的员工轮类。可用性条目将是连续的，绝不会出现同一员工背靠背的可用性条目，也不会出现同一员工重叠的可用性条目。

基本上，我需要获取 ( availabilities.start <= shifts.start AND availabilities.end >= shifts.end ) 不成立的类次行。换句话说，我需要从类次表中获取可用性条目未完全包含的行。

它需要考虑这些可能性:

• 在可用之前开始的类次
• 有空后结束的类次
• 轮类期间没有任何空缺的类次

如果使用存储过程而不是查询效率更高，我可以接受。

表格如下:

CREATE TABLE availabilities (`id` int primary key, `employee_id` int, `start` datetime, `end` datetime);
CREATE TABLE shifts (`id` int primary key, `employee_id` int, `start` datetime, `end` datetime);

这是一些示例数据:

INSERT INTO availabilities
    (`employee_id`, `start`, `end`)
VALUES
    (1, '2015-01-01 08:00:00', '2015-01-01 09:00:00'),
    (1, '2015-01-02 08:00:00', '2015-01-02 10:00:00'),
    (2, '2015-01-03 08:00:00', '2015-01-03 14:00:00'),
    (2, '2015-01-04 08:00:00', '2015-01-04 18:00:00')
;

INSERT INTO shifts
    (`employee_id`, `start`, `end`)
VALUES
    (1, '2015-01-01 08:00:00', '2015-01-01 09:00:00'),
    (1, '2015-01-02 08:30:00', '2015-01-02 10:00:00'),
    (1, '2015-01-02 10:30:00', '2015-01-02 12:00:00'),
    (2, '2015-01-03 08:00:00', '2015-01-03 09:00:00'),
    (2, '2015-01-03 09:00:00', '2015-01-03 14:30:00'),
    (2, '2015-01-04 09:30:00', '2015-01-04 17:30:00'),
    (2, '2015-01-05 08:00:00', '2015-01-05 10:00:00')
;

我希望输出第 3、5 和 7 个类次，因为它们不可用。

我已经尝试过类似下面的方法(以及许多其他方法)，但是所有这些方法要么给出误报，要么漏掉轮类。

SELECT s.*  FROM `shifts` AS `s`
LEFT JOIN `availabilities` AS `a` ON `s`.`employee_id` = `a`.`employee_id`
WHERE (NOT(a.start <= s.start AND a.end >= s.end) OR a.id IS NULL);

Answer 1

这有帮助吗？

select *
from shifts as s
where not exists (
    select 1
    from availabilities as a
    where a.start <= s.start AND a.end >= s.end and a.employee_id = s.employee_id
)