помощь в формировании запроса mysql для поиска бесплатных (доступных) мест/ресурсов для заданного диапазона дат

У меня есть таблицы и данные, подобные этому:

venues table contains : id

+----+---------+
| id | name    |
+----+---------+
|  1 | venue 1 |
|  2 | venue 2 |
---------------

event_dates : id, event_id, event_from_datetime, event_to_datetime, venue_id
+----+----------+---------------------+---------------------+----------+
| id | event_id | event_from_datetime | event_to_datetime   | venue_id |
+----+----------+---------------------+---------------------+----------+
|  1 |        1 | 2009-12-05 00:00:00 | 2009-12-07 00:00:00 |        1 |
|  2 |        1 | 2009-12-09 00:00:00 | 2009-12-12 00:00:00 |        1 |
|  3 |        1 | 2009-12-15 00:00:00 | 2009-12-20 00:00:00 |        2 |
+----+----------+---------------------+---------------------+----------+

Это мое требование: мне нужны площадки, которые будут свободны 06.12.2009, 00:00:00, т.е.

я должен получить

|место_id|

|2 |

В настоящее время у меня есть следующий запрос,

select ven.id , evtdt.event_from_datetime, evtdt.event_to_datetime 
from venues ven 
left join event_dates evtdt 
on (ven.id=evtdt.venue_id) 
where evtdt.venue_id is null 
or not ('2009-12-06 00:00:00' between evtdt.event_from_datetime 
                                  and evtdt.event_to_datetime);
+----+---------------------+---------------------+
| id | event_from_datetime | event_to_datetime   |
+----+---------------------+---------------------+
|  1 | 2009-12-09 00:00:00 | 2009-12-12 00:00:00 |
|  2 | 2009-12-15 00:00:00 | 2009-12-20 00:00:00 |
|  3 | NULL                | NULL                |
|  5 | NULL                | NULL                |
+----+---------------------+---------------------+

Если вы обратите внимание на результаты, они не включают идентификатор места проведения 1, где дата находится между 2009-12-06 00:00:00, но показывает другие бронирования. Пожалуйста, помогите мне исправить этот запрос.

Заранее спасибо.


mysql date join date-range
person CodeTweetie    schedule 12.01.2010    source источник
comment
@Anitha: Не могли бы вы отредактировать свой вопрос и добавить, какой результат вы ожидаете и почему?   -  person Peter Lang    schedule 12.01.2010
comment
Привет, Петр, спасибо за ответ. Я отредактировал вопрос. Пожалуйста, дайте мне знать, если это имеет смысл сейчас. Еще раз спасибо.   -  person CodeTweetie    schedule 12.01.2010
comment
Кроме того, опубликованные вами результаты, в частности, не являются результатами выполнения предоставленного вами запроса, как указано в моем сообщении ниже...   -  person vicatcu    schedule 12.01.2010

Ответы (3)


arrow_upward
1
arrow_downward 

or not ('2009-12-06 00:00:00' between evtdt.event_from_datetime 
                              and evtdt.event_to_datetime);

6.12.2009 находится между 5.12.09 и 7.12.09... поэтому место проведения_id 1 исключается... что вы пытаетесь извлечь из данных точно?

Созданный вами запрос на соединение говорит: возьмите таблицу мест проведения и для каждой ее строки, имеющей совпадающий идентификатор места проведения, сделайте копию строки таблицы мест проведения и добавьте совпадающую строку. Итак, если вы только что сделали:

select * 
  from venues ven 
  left join event_dates evtdt 
  on (ven.id=evtdt.venue_id);

Это даст:

+----+---------+------+----------+---------------------+---------------------+----------+
| id | name    | id   | event_id | event_from_datetime | event_to_datetime   | venue_id |
+----+---------+------+----------+---------------------+---------------------+----------+
|  1 | venue 1 |    1 |        1 | 2009-12-05 00:00:00 | 2009-12-07 00:00:00 |        1 |
|  1 | venue 1 |    2 |        1 | 2009-12-09 00:00:00 | 2009-12-12 00:00:00 |        1 |
|  2 | venue 2 |    3 |        1 | 2009-12-15 00:00:00 | 2009-12-20 00:00:00 |        2 |
+----+---------+------+----------+---------------------+---------------------+----------+

Если вы затем добавили свое условие, в котором говорится, что интересующая дата не находится между начальной и конечной датой события, запрос будет выглядеть так:

select * 
  from venues ven 
  left join event_dates evtdt 
  on (ven.id=evtdt.venue_id) 
  where not ('2009-12-06' between evtdt.event_from_datetime and evtdt.event_to_datetime)

Что дает результат:

+----+---------+------+----------+---------------------+---------------------+----------+
| id | name    | id   | event_id | event_from_datetime | event_to_datetime   | venue_id |
+----+---------+------+----------+---------------------+---------------------+----------+
|  1 | venue 1 |    2 |        1 | 2009-12-09 00:00:00 | 2009-12-12 00:00:00 |        1 |
|  2 | venue 2 |    3 |        1 | 2009-12-15 00:00:00 | 2009-12-20 00:00:00 |        2 |
+----+---------+------+----------+---------------------+---------------------+----------+

Это мои фактические экспериментальные результаты с вашими данными в MySQL.

Если вы хотите получить свободную площадку на предполагаемую дату, вы должны написать что-то вроде:

  select ven.id, SUM('2009-12-06' between evtdt.event_from_datetime and evtdt.event_to_datetime) as num_intersects 
    from venues ven left join event_dates evtdt on (ven.id=evtdt.venue_id) 
    group by ven.id
    having num_intersects = 0;

что дает:

+----+----------------+
| id | num_intersects |
+----+----------------+
|  2 |              0 |
+----+----------------+

это также дает правильный ответ (без изменений) в случае, когда у вас есть место без событий в таблице event_date.

person vicatcu    schedule 12.01.2010

arrow_upward
0
arrow_downward

Полагаю, если вы удалите не из

or not ('2009-12-06 00:00:00' between evtdt.event_from_datetime 
                                  and evtdt.event_to_datetime)

это затем вернет строку 1 из дат событий, но не другие строки даты события.

Я говорю «в догадках», потому что ваше предложение where немного сложно понять. Может быть, вы имеете в виду

select ven.id , evtdt.event_from_datetime, evtdt.event_to_datetime 
from venues ven 
left join event_dates evtdt 
on (ven.id=evtdt.venue_id) 
where '2009-12-06 00:00:00' between evtdt.event_from_datetime 
                                  and evtdt.event_to_datetime;
person Steve De Caux    schedule 12.01.2010
comment
Привет, Стив, я снова отредактировал свой вопрос для большей ясности. Кроме того, левое соединение предназначено для получения мест, на которых может вообще не быть бронирований (имеется в виду отсутствие строк в таблице event_dates, но запись строки в таблице мест). Спасибо за ответ. - person CodeTweetie; 12.01.2010

arrow_upward

arrow_downward

person    schedule
comment
Не вопреки, но я считаю, что вы могли бы избежать использования подзапроса там, где достаточно операции соединения и фильтрации. Я думаю, что вы можете получить значительный прирост производительности, правильно проиндексировав свои таблицы и выполнив соединение, которое вы изначально предложили. - person vicatcu; 12.01.2010
comment
@vicatcu: MySQL достаточно умен, чтобы оптимизировать подзапросы. Пожалуйста, прочитайте эту статью в моем блоге: explainextended.com/2009/09/18/ - person Quassnoi; 12.01.2010