Mysql 检查时间之间的可用性

Question

我需要检查 10:00 到 10:30 之间是否有可用时间(应返回 0 行)或在 12:30 到 17:00 之间预订(应返回 1 行)

CREATE TABLE IF NOT EXISTS `clase` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fechaClase` date NOT NULL,
  `horaInicio` time NOT NULL, 
  `horaFin` time NOT NULL,
  PRIMARY KEY (`id`));

  INSERT INTO `clase` (`id`, `fechaClase`, `horaInicio`,  `horaFin`) VALUES
    (1, '2019-10-28', '10:30:00', '12:00:00'),
    (2, '2019-10-28', '09:00:00', '10:00:00'),
    (3, '2019-10-28', '13:30:00', '15:00:00');

https://www.db-fiddle.com/f/jefiEkboU7qo4baEMW25VV/0

Answer 1

存在三种情况，开始预订时间属于已预订范围，结束预订时间属于已预订范围，开始和结束预订时间跨越已预订范围。左连接将发现已预订/未预订，空测试将打印消息。

drop table if exists clase;

CREATE TABLE IF NOT EXISTS `clase` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fechaClase` date NOT NULL,
  `horaInicio` time NOT NULL, 
  `horaFin` time NOT NULL,
  PRIMARY KEY (`id`));

  INSERT INTO `clase` (`id`, `fechaClase`, `horaInicio`,  `horaFin`) VALUES
    (1, '2019-10-28', '10:30:00', '12:00:00'),
    (2, '2019-10-28', '09:00:00', '10:00:00'),
    (3, '2019-10-28', '13:30:00', '14:00:00');

select distinct
        case when c.id is null then concat(s.horainicio,':',s.horafin,' available')
        else concat(s.horainicio,':',s.horafin,' not available')
        end as sta
from
(    
select '10:00' as horaInicio, '10:30' as horafin
union 
select '14:30' ,'17:00'
union
select '13:00' ,'15:00'
) s  
left join clase c on (s.horainicio between c.horaInicio and c.horafin) or
                            (s.horafin    between c.horaInicio and c.horafin) or
                            (c.horainicio between s.horainicio and s.horafin) or
                            (c.horafin    between s.horainicio and s.horafin)


+---------------------------+
| sta                       |
+---------------------------+
| 10:00:10:30 not available |
| 13:00:15:00 not available |
| 14:30:17:00 available     |
+---------------------------+
3 rows in set (0.06 sec)