mysql - SQL转移到可用性之外

我正在尝试将一个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);

最佳答案

这有帮助吗？

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
)