MySQL: изменить пользовательскую переменную для каждой выбранной строки

Question

Я пытаюсь выбрать первые десять пустых временных интервалов между встречами в базе данных MySQL.

Таблица встреч имеет в основном 3 поля: встречи_Инт INT, startDateTime DATETIME и endDateTime DATETIME.

Мы можем представить некоторые данные, подобные этим (ради простоты, я оставил часть даты вне даты и времени, поэтому давайте рассмотрим, что эти часы находятся в одном и том же дне). Также данные упорядочены по startDateTime:

4 | 09:15:00 | 09:30:00
5 | 09:30:00 | 09:45:00
8 | 10:00:00 | 10:15:00
3 | 10:30:00 | 10:45:00
7 | 10:45:00 | 11:00:00
2 | 11:00:00 | 11:15:00
1 | 11:30:00 | 12:00:00

Поэтому моя цель - извлечь:

00:00:00 | 09:15:00
09:45:00 | 10:00:00
10:15:00 | 10:30:00
11:15:00 | 11:30:00

В конечном итоге делать это:

SET @myStart = '2012-10-01 09:15:00';
SET @myEnd = NULL;
SET @prevEnd = NULL;
SELECT a.endDateTime, b.startDateTime, @myStart := a.endDateTime
FROM appointment a, appointment b, (
    SELECT @myEnd := min(c.startDateTime) 
    FROM appointment c 
    WHERE c.startDateTime >= @myStart
    ORDER BY startDateTime ASC
) as var , 
(SELECT @prevEnd := NULL) v
WHERE a.appointment_id = (
    SELECT appointment_id 
    FROM (
        SELECT appointment_id, max(endDateTime), @prevEnd := endDateTime 
        FROM appointment d
        WHERE (@prevEnd IS NULL OR @prevEnd = d.startDateTime) 
            AND d.startDateTime >= @myEnd
    ) as z
) 
    AND b.startDateTime > a.endDateTime 
ORDER BY b.startDateTime ASC LIMIT 0,10;

Это не возвращает никакого результата. Я предполагаю, что это из-за неправильной инициализации моих пользовательских переменных (только что обнаружил их, и я могу использовать их совершенно неправильно).

Если я запускаю только первый подзапрос, целью которого является инициализация @myEnd при первом назначении после @myStart, я вижу, что на самом деле он возвращает 09:15:00.

Второй подзапрос (SELECT @prevEnd := NULL) v предназначен для установки @prevEnd обратно в NULL каждый раз, когда в основном запросе выбирается строка. Я не совсем уверен, что это так работает...

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

Есть ли у вас какие-либо советы о том, что я мог бы сделать, чтобы исправить запрос, о том, как я мог / должен сделать это иначе или вообще, возможно ли это или нет?

Большое спасибо заранее.

Редактировать: я отредактировал это так:

SELECT * 
    FROM (
        SELECT COALESCE( s1.endDateTime,  '0000-00-00 00:00:00' ) AS myStart, MIN( s2.startDateTime ) AS minSucc
        FROM appointment s1
        RIGHT JOIN appointment s2 ON s1.endDateTime < s2.startDateTime
            AND s1.radiologyroom_id = s2.radiologyroom_id
        WHERE s1.startDateTime >=  '2012-10-01 00:00:00'
            AND s1.radiologyroom_id =174
            AND s1.endDateTime <  '2013-01-01 00:00:00'
        GROUP BY myStart
        ORDER BY s1.startDateTime
    )s
WHERE NOT 
    EXISTS (
        SELECT NULL 
        FROM appointment
        WHERE startDateTime >= myStart
            AND endDateTime <= minSucc
            AND radiologyroom_id =174
        ORDER BY startDateTime
    )

и он получает 369 строк за 14,6 секунд из 6530 записей

Answer 1

Если между пробелами нет ids, а также id всегда увеличивается, вы можете использовать это:

SELECT coalesce(s1.endDateTime, '0000-00-00 00:00:00'), s2.startDateTime
FROM
  slots s1 right join slots s2
  on s1.appointment_id=s2.appointment_id-1
WHERE coalesce(s1.endDateTime, '0000-00-00 00:00:00')<s2.startDateTime
LIMIT 10

РЕДАКТИРОВАТЬ: вы также можете попробовать это:

SELECT * FROM
  (SELECT
     coalesce(s1.endDateTime, '0000-00-00 00:00:00') as start,
     min(s2.startDateTime) minSucc
   from slots s1 right join slots s2
        on s1.endDateTime<s2.startDateTime
   group by start) s
WHERE
  not exists (select null
              from slots
              where startDateTime>=start
              and endDateTime<=minSucc)

РЕДАКТИРОВАТЬ 2: Я признаю, что я не очень практичен с запросами с переменными, но похоже, что это может работать:

select d1, d2 from (
  select
    @previous_end as d1,
    s.startDateTime as d2,
    @previous_end:=s.endDateTime
  from (select startDateTime, endDateTime from slots order by startDateTime) s,
       (select @previous_end := '0000-00-00 00:00:00') t) s
where d1<d2