mysql 结果来自 table1，其中 table2 不匹配并且在 start_datetime 和 start_datetime + 8 小时之间

Question

我想从表 employees 中查询员工 ID 列表，但排除已位于名为 employee_schedule 的单独表中的员工 ID如果它落在特定的 datetime 之间和同样的datetime + 8 hours 。由于其他原因，我没有使用日期时间来表示轮类结束。

SELECT user_id
FROM employees AS t1
LEFT OUTER JOIN employee_schedule AS t2 ON t1.user_id = t2.assigned_guard_id
WHERE t2.assigned_guard_id IS NULL
AND ('2017-10-23 18:00:00' BETWEEN t2.shift_start 
AND ADDTIME(t2.shift_start, '12:00:00'))

结果:

`employees` |  `employee_schedule`
----------------------------------------------------------------------------------------
id          |  guard_assigned_id, shift_start(datetime), shift_duration(INT, minutes)
----------------------------------------------------------------------------------------
17          |  17, '2017-10-23 18:00:00', '480'
19          |  
22          |
----------------------------------------------------------------------------------------

结果应仅显示 19和22 ，因为 17 出现在 shift_start 之间的时间表中和shift_start + shift_duration .

我的查询返回 0 个结果。

Answer 1

您的范围条件正在将 LEFT JOIN 转换为 INNER JOIN。对于同一行，这两个条件永远不可能成立。您需要将第二个条件移至 LEFT JOIN 的 ON 子句。所以应该是:

SELECT user_id
FROM employees AS t1
LEFT OUTER JOIN employee_schedule AS t2
    ON  t1.user_id = t2.assigned_guard_id
    AND '2017-10-23 18:00:00' BETWEEN t2.shift_start
        AND ADDTIME(t2.shift_start, '12:00:00')
WHERE t2.assigned_guard_id IS NULL

请注意，引擎可能无法优化范围条件以使用索引。最好写成

AND t2.shift_start <= '2017-10-23 18:00:00'
AND t2.shift_start >= SUBTIME('2017-10-23 18:00:00', '12:00:00')

或

AND t2.shift_start BETWEEN SUBTIME('2017-10-23 18:00:00', '12:00:00')
    AND '2017-10-23 18:00:00'

如果您想使用shift_duration而不是恒定时间，您可以使用

AND t2.shift_start <= '2017-10-23 18:00:00'
AND t2.shift_start >= '2017-10-23 18:00:00' - INTERVAL shift_duration MINUTE