查询以查找可用的时隙

Question

我有一个叫appointments的表和一个叫shifts的表。在表appointments中，有shift_id，所以它看起来像这样：

id | start_date | end_date | shift_id ( FK )

一个示例班次是09:00到17:00，其中包含几个约会：

09:30 - 10:10 | 11:30 - 11:50 | 13:00 - 14:00

我目前正在使用这个查询来获取可用的时隙，但它有一个边缘情况问题。它找不到从9点到9点30分的开始。

SELECT Available_from, Available_to
FROM (
SELECT @lasttime_to AS Available_from, start_date AS Available_to, @lasttime_to := end_date
FROM (SELECT start_date, end_date
FROM appointments
WHERE shift_id = " . $shiftData->id . "
AND end_date <= '" . $shiftData->end_date->toDateTimeString() . "'
AND start_date >= '" . $shiftData->start_date->toDateTimeString() . "'
UNION ALL
SELECT '". $shiftData->start_date->toDateTimeString() ."', '". $shiftData->start_date->toDateTimeString() ."'
ORDER BY end_date 

) e
JOIN (SELECT @lasttime_to := NULL) init) x
WHERE Available_to > DATE_ADD(Available_from, INTERVAL " . $serviceDurations . " MINUTE)

这里面少了什么？如何开始显示在查询中？

http://sqlfiddle.com/#！9/e5292d/2

Answer 1

首先，处理原始SQL(在fiddle中(。

对于第一行，@lasttime_to为NULL并导致问题。

需要使用"；换档开始时间"；条件为null的情况(第一行(。

另一种方法参见LAG。这正是@variable黑客试图取代的，在LAG在MySQL中可用之前。

试试这个：

SELECT Available_from, Available_to
FROM (
SELECT COALESCE(@lasttime_to, '2022-03-15 10:00:00') AS Available_from, start_date AS Available_to, @lasttime_to := end_date
FROM (SELECT start_date, end_date
FROM appointments
WHERE shift_id = 4600
AND end_date <= '2022-03-15 17:00:00'
AND start_date >= '2022-03-15 10:00:00'
UNION ALL
SELECT '2022-03-15 17:00:00', '2022-03-15 17:00:00'
ORDER BY start_date 
) e
JOIN (SELECT @lasttime_to := NULL) init) x
WHERE Available_to > DATE_ADD(Available_from, INTERVAL 20 MINUTE)
;

结果：

Available_from	Available_to
2022-03-15 10:00:00	2022-03-15 10:30:00
2022-03-15 12:15:00	2022-03-15 15:00:00
2022-03-15 15:40:00	2022-03-15 17:00:00