SQL-запрос о встречах

Question

Предположим, у нас есть эта таблица..

CREATE TABLE `appointments` (
  `idappointments` int(11) NOT NULL AUTO_INCREMENT,
  `timeStart` time DEFAULT NULL,
  `timeEnd` time DEFAULT NULL,
  PRIMARY KEY (`idappointments`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8$$

предположение

Предположим, что диапазон между timeStart и timeEnd больше не может существовать... Я имею в виду, что если мы пересечем все диапазоны в таблице, результатом будет EMPTY,0, null. Назначение не может сосуществовать с другим..

Так что я хочу сделать, это предложение времени, если желаемое время занято... Предложение до и предложение после желаемого времени...

Поэтому мне было интересно вместо написания кода сделать это, если бы я мог написать запрос SQL, чтобы найти ближайшие пустые диапазоны......

Пример: timeStart - NEAREST_TO_TIMESTART_TIMEEND > "10 минут", тогда как продолжительность 10 минут

Answer 1

MySQL не имеет рекурсивной функциональности, поэтому вам нужно использовать табличный трюк NUMBERS -

1. Создайте таблицу, которая содержит только увеличивающиеся числа - это легко сделать с помощью auto_increment:

   DROP TABLE IF EXISTS `example`.`numbers`;
   CREATE TABLE  `example`.`numbers` (
     `id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

2. Заполните таблицу, используя:

   INSERT INTO NUMBERS
     (id)
   VALUES
     (NULL)
   

   ... столько ценностей, сколько вам нужно.

3. Используйте DATE_ADD для построения списка дат, увеличивая количество дней на основе значения NUMBERS.id. Замените "2010-01-01" и "2010-01-02" на соответствующие даты начала и окончания (но используйте один и тот же формат, ГГГГ-ММ-ДД ЧЧ: ММ: СС) -

   SELECT x.dt
     FROM (SELECT TIME(DATE_ADD('2010-01-01', INTERVAL (n.id - 1) * 15 MINUTE)) AS dt
             FROM numbers n
            WHERE DATE_ADD('2010-01-01', INTERVAL (n.id - 1) * 15 MINUTE) <= '2010-01-02' ) x
   

4. СЛЕДУЮЩИЙ ПРИСОЕДИНЯЙТЕСЬ к своей таблице данных, основанной на части datetime.

Это покажет вам первый доступный слот:

       SELECT x.dt
      FROM (SELECT TIME(DATE_ADD('2010-01-01', INTERVAL (n.id - 1) * 15 MINUTE)) AS dt
              FROM numbers n
             WHERE DATE_ADD('2010-01-01', INTERVAL (n.id - 1) * 15 MINUTE) <= '2010-01-02' ) x
    LEFT JOIN APPOINTMENTS a ON x.dt BETWEEN a.timestart AND a.timeend
        WHERE a.idappoinment IS NULL
          AND x.dt > @your_minimum_datetime
     ORDER BY x.dt
        LIMIT 1

Это покажет вам доступность на весь день:

       SELECT x.dt,
              CASE 
                WHEN a.idappoinment IS NULL THEN 'available'
                ELSE 'booked'
              END AS isbooked 
      FROM (SELECT TIME(DATE_ADD('2010-01-01', INTERVAL (n.id - 1) * 15 MINUTE)) AS dt
              FROM numbers n
             WHERE DATE_ADD('2010-01-01', INTERVAL (n.id - 1) * 15 MINUTE) <= '2010-01-02' ) x
    LEFT JOIN APPOINTMENTS a ON x.dt BETWEEN a.timestart AND a.timeend
     ORDER BY x.dt

Почему цифры, а не даты?

Просто - даты могут быть сгенерированы на основе числа, как в приведенном мной примере. Это также означает использование одной таблицы, скажем, по одной для каждого типа данных.