mysql - Sql查询确定未分配时间

Question

我有一个表，其中包含 BOOKING、START_TIME、END_TIME 列。假设 BOOKING 'A' 的几行包含如下数据：

       BOOKING, START_TIME, END_TIME

         A    | 2PM      | 3PM
         A    | 6PM      | 7PM

我想知道下午 2 点到 7 点之间的时间 A 的预订没有完成？

例如，在上述情况下，下午 4-6 点是未完成预订的时间。

标签： mysqlsql

Answer 1

SQL 演示

SELECT t1.`BOOKING`
     , t1.`START_TIME`, t1.`END_TIME`
     , t2.`START_TIME`, t2.`END_TIME`
     , DATE_ADD(t1.`END_TIME`, INTERVAL 1 HOUR)  as  unallocated_start
     , t2.`START_TIME`as  unallocated_end
FROM Table1 t1
JOIN Table1 t2
  ON t1.`END_TIME` < t2.`START_TIME`
 AND t1.`BOOKING` = t2.`BOOKING`
WHERE NOT EXISTS (SELECT 1 
                  FROM Table1 t3
                  WHERE t3.`BOOKING` = t1.`BOOKING`
                    AND t3.`START_TIME` > t1.`END_TIME` 
                    AND t3.`START_TIME` < t2.`START_TIME`);

输出

| BOOKING | START_TIME | END_TIME | START_TIME | END_TIME | unallocated_start | unallocated_end |
|---------|------------|----------|------------|----------|-------------------|-----------------|
|       A |   14:00:00 | 15:00:00 |   18:00:00 | 19:00:00 |          16:00:00 |        18:00:00 |
|       A |   18:00:00 | 19:00:00 |   20:00:00 | 23:00:00 |          20:00:00 |        20:00:00 |
|       B |   04:00:00 | 10:00:00 |   13:00:00 | 19:00:00 |          11:00:00 |        13:00:00 |