Находите диапазон диапазонов дат в пределах одной таблицы, для конкретного пользователя MySQL

Я отнюдь не эксперт по MySQL, поэтому я ищу любую помощь по этому вопросу.

Мне нужно выполнить простой тест (в принципе), у меня есть эта (упрощенная) таблица:

tableid | userid | car | From | To -------------------------------------------------------- 1 | 1 | Fiesta | 2015-01-01 | 2015-01-31 2 | 1 | MX5 | 2015-02-01 | 2015-02-28 3 | 1 | Navara | 2015-03-01 | 2015-03-31 4 | 1 | GTR | 2015-03-28 | 2015-04-30 5 | 2 | Focus | 2015-01-01 | 2015-01-31 6 | 2 | i5 | 2015-02-01 | 2015-02-28 7 | 2 | Aygo | 2015-03-01 | 2015-03-31 8 | 2 | 206 | 2015-03-29 | 2015-04-30 9 | 1 | Skyline | 2015-04-29 | 2015-05-31 10 | 2 | Skyline | 2015-04-29 | 2015-05-31 

Мне нужно найти две вещи здесь:

  1. Если какой-либо пользователь имеет совпадение даты в своих автомобильных присвоениях более одного дня (окончание задания может быть в тот же день, что и начало нового задания).
  2. Кто-нибудь из двух пользователей попытался получить тот же самый автомобиль, назначенный на ту же дату, или диапазоны дат перекрываются для них на одном автомобиле.

Поэтому запрос (или запросы), который я ищу, должен возвращать эти строки:

 tableid | userid | car | From | To -------------------------------------------------------- 3 | 1 | Navara | 2015-03-01 | 2015-03-31 4 | 1 | GTR | 2015-03-28 | 2015-04-30 7 | 2 | Aygo | 2015-03-01 | 2015-03-31 8 | 2 | 206 | 2015-03-29 | 2015-04-30 9 | 1 | Skyline | 2015-04-29 | 2015-05-31 10 | 2 | Skyline | 2015-04-29 | 2015-05-31 

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

Я проводил исследования и несколько часов тестирования, но я не могу получить нигде рядом с результатом, который я хочу.

SQLFiddle с приведенными выше данными теста

Я пробовал эти сообщения btw (они были не совсем то, что мне нужно, но были достаточно близки, или так я думал):

Сравнение двух диапазонов дат в одной таблице

Как сравнить значения текстовых столбцов из одной таблицы

Это было самое близкое решение, которое я смог найти, но когда я попробовал его на одной таблице (соединяясь с таблицей для себя), я получал сумасшедшие результаты: проверка таблицы на временное перекрытие?

РЕДАКТИРОВАТЬ

В качестве временного решения я адаптировал другой подход, аналогичный положению, которое я нашел во время своих исследований (см. Выше). Теперь я проверю, совпадает ли новая дата аренды / назначения аренды с любым диапазоном дат в таблице. Если это так, я сохраню id (s) строк, с которыми совпадает дата. Таким образом, по крайней мере, я смогу отмечать перекрытия и разрешить пользователю просматривать помеченные строки и разрешать любые совпадения вручную.

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

Редактировать # 2

Оба ответа являются блестящими и для всех, кто считает, что этот пост имеет ту же проблему, что и я, читайте их и смотрите на скрипки! 🙂 В них вошло много удивительной мозговой работы! Временно мне пришлось пойти с решением, о котором я упоминаю в № 1 «Редактирование», но я буду адаптировать свои запросы, чтобы пойти с подходом @Ryan Vincent + @philipxy edit / comments об игнорировании начального однократного перекрытия.

Вот первая часть: Перекрытие автомобилей на пользователя …

SQLFiddle – коррелированный запрос и объединение запросов

Вторая часть – более одного пользователя в одном автомобиле в одно и то же время: SQLFiddle – коррелированный запрос и объединение запросов . Запрос ниже …

Я использую коррелированные запросы:

Вероятно, вам понадобятся индексы для userid и ‘car’. Однако, пожалуйста, проверьте «план объяснения», чтобы узнать, как он обращается к данным. И просто попробуйте 🙂

Перекрытие автомобилей на одного пользователя

Запрос:

 SELECT `allCars`.`userid` AS `allCars_userid`, `allCars`.`car` AS `allCars_car`, `allCars`.`From` AS `allCars_From`, `allCars`.`To` AS `allCars_To`, `allCars`.`tableid` AS `allCars_id` FROM `cars` AS `allCars` WHERE EXISTS (SELECT 1 FROM `cars` AS `overlapCar` WHERE `allCars`.`userid` = `overlapCar`.`userid` AND `allCars`.`tableid` <> `overlapCar`.`tableid` AND NOT ( `allCars`.`From` >= `overlapCar`.`To` /* starts after outer ends */ OR `allCars`.`To` <= `overlapCar`.`From`)) /* ends before outer starts */ ORDER BY `allCars`.`userid`, `allCars`.`From`, `allCars`.`car`; 

Результаты:

 allCars_userid allCars_car allCars_From allCars_To allCars_id -------------- ----------- ------------ ---------- ------------ 1 Navara 2015-03-01 2015-03-31 3 1 GTR 2015-03-28 2015-04-30 4 1 Skyline 2015-04-29 2015-05-31 9 2 Aygo 2015-03-01 2015-03-31 7 2 206 2015-03-29 2015-04-30 8 2 Skyline 2015-04-29 2015-05-31 10 

Почему это работает? или Как я думаю об этом:

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

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

Таким образом, для каждой пользовательской записи (AllCars) проверьте полную таблицу (overlapCar), чтобы увидеть, можно ли найти другую запись, которая перекрывается во время текущей записи. Если мы найдем его, выберите текущую запись, которую мы проверяем (на всех картах).

Поэтому проверка совпадения :

  • allCars userid allCars userid overLap должны быть одинаковыми
  • автомобильная запись allCars и запись автомобиля с overlap должны быть разными
  • временной диапазон overLap диапазон времени overLap должны перекрываться.

    Проверка временного диапазона:

    Вместо проверки на перекрывающиеся времена используйте положительные тесты. Самый простой подход - проверить, не перекрывается ли он и применить к нему NOT .

Один автомобиль с более чем одним пользователем одновременно ...

Запрос:

 SELECT `allCars`.`car` AS `allCars_car`, `allCars`.`userid` AS `allCars_userid`, `allCars`.`From` AS `allCars_From`, `allCars`.`To` AS `allCars_To`, `allCars`.`tableid` AS `allCars_id` FROM `cars` AS `allCars` WHERE EXISTS (SELECT 1 FROM `cars` AS `overlapUser` WHERE `allCars`.`car` = `overlapUser`.`car` AND `allCars`.`tableid` <> `overlapUser`.`tableid` AND NOT ( `allCars`.`From` >= `overlapUser`.`To` /* starts after outer ends */ OR `allCars`.`To` <= `overlapUser`.`From`)) /* ends before outer starts */ ORDER BY `allCars`.`car`, `allCars`.`userid`, `allCars`.`From`; 

Результаты:

 allCars_car allCars_userid allCars_From allCars_To allCars_id ----------- -------------- ------------ ---------- ------------ Skyline 1 2015-04-29 2015-05-31 9 Skyline 2 2015-04-29 2015-05-31 10 

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

Ввиду комментариев от @philipxy, о временных диапазонах, требующих проверки «больше или равно», я обновил здесь код. Я не изменил SQLFiddles .

Для каждой таблицы ввода и вывода наш смысл . Т.е. шаблон утверждения, параметризованный именами столбцов, aka predicate , что строка делает в истинное или ложное утверждение, ака предложение . Таблица содержит строки, которые превращают его предикат в истинное предложение. Т.е. строки, которые делают истинное предложение, идут в таблице и строках, которые делают ложное предложение. Например, для таблицы ввода:

 rental [tableid] was user [userid] renting car [car] from [from] to [to] 

Затем выражаем предикат выходной таблицы в терминах предиката входной таблицы. Не используйте описания, подобные вашим 1 и 2:

  1. Если какой-либо пользователь имеет совпадение даты в своих автомобильных присвоениях более одного дня (окончание задания может быть в тот же день, что и начало нового задания).

Вместо этого найдите предикат, который произвольная строка укажет в таблице:

 rental [tableid] was user [user] renting car [car] from [from] to [to] in self-conflict with some other rental 

Чтобы СУБД вычислить строки, которые делают это истинным, мы должны выразить это в терминах нашего предиката (-ов) плюс литералы и условия:

 -- query result holds the rows where FOR SOME t2.tableid, t2.userid, ...: rental [t1.tableid] was user [t1.userid] renting car [t1.car] from [t1.from] to [t1.to] AND rental [t2.tableid] was user [t2.userid] renting car [t2.car] from [t2.from] to [t2.to] AND [t1.userid] = [t2.userid] -- userids id the same users AND [t1.to] > [t2.from] AND ... -- tos/froms id intervals with overlap more than one day ... 

(Внутри оператора SQL SELECT кросс-произведение таблиц JOIN ed имеет имена столбцов alias формы . Column.Подумайте, как другой символ, разрешенный в именах столбцов. Наконец, предложение SELECT сбрасывает alias . S.)

Мы преобразуем предикат запроса в SQL-запрос, который вычисляет строки, которые делают его истинным:

  • Предикат таблицы заменяется псевдонимом таблицы.
  • Чтобы использовать один и тот же предикат / таблицу несколько раз, используйте псевдонимы.
  • Изменение old столбца в new в предикате добавляет AND old = new .
  • AND предикатов заменяется JOIN .
  • OR предикатов заменяется UNION .
  • AND NOT предикатов заменяется EXCEPT , MINUS или подходящей LEFT JOIN .
  • AND condition заменяется condition WHERE или ON .
  • Для предиката true FOR SOME columns to drop или когда THERE EXISTS columns to drop , columns to keep SELECT DISTINCT columns to keep .
  • И т.д. (см. Это .)

Следовательно (завершение эллипсов):

 SELECT DISTINCT t1.* FROM t t1 JOIN t t2 ON t1.userid = t1.userid -- userids id the same users WHERE t1.to > t2.from AND t2.to > t1.from -- tos/froms id intervals with overlap more than one day AND t1.tableid <> t2.tableid -- tableids id different rentals 
  1. Кто-нибудь из двух пользователей попытался получить тот же самый автомобиль, назначенный на ту же дату, или диапазоны дат перекрываются для них на одном автомобиле.

Поиск предиката, который в любой строке указывается в таблице:

 rental [tableid] was user [user] renting car [car] from [from] to [to] in conflict with some other user's rental 

В терминах нашего предиката (-ов) плюс литералы и условия:

 -- query result holds the rows where FOR SOME t2.* rental [t1.tableid] was user [t1.userid] renting car [t1.car] from [t1.from] to [t1.to] AND rental [t2.tableid] was user [t2.userid] renting car [t2.car] from [t2.from] to [t2.to] AND [t1.userid] <> [t2.userid] -- userids id different users AND [t1.car] = [t2.car] -- .cars id the same car AND [t1.to] >= [t2.from] AND [t2.to] >= [t1.from] -- tos/froms id intervals with any overlap AND [t1.tableid] <> [t2.tableid] -- tableids id different rentals 

UNION запросов для предикатов 1 и 2 возвращает строки, для которых predicate 1 OR predicate 2 .

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

PS Хорошо всегда иметь проверку данных для случаев с краем и без края для условия, являющегося истинным и ложным. Например, попробуйте выполнить запрос 1 с GTR, начиная с 31-го, перекрывая только один день, что не должно быть самоконфликтным.

Запрос PPS с использованием повторяющихся строк, как и для NULL, имеет довольно сложные значения запроса. Трудно сказать, когда кортеж входит или остается вне стола и сколько раз. Для запросов, которые имеют простые интуитивные значения для моих соответствий, у них не может быть дубликатов. Здесь SQL, к сожалению, отличается от реляционной модели. На практике люди полагаются на идиомы, когда допускают нечеткие строки, и они полагаются на строки, отличающиеся из-за ограничений. Например, присоединяется к UNIQUE столбцам на UNIQUE, PKs & FKs. Например: последний шаг DISTINCT работает только в разное время, чем версия, которая ему не нужна; время может быть или не быть важной проблемой реализации, влияющей на формулировку, выбранную для данного предиката / результата.

  • Войдите в базу данных, используя log4j
  • Каталог проекта Visual Studio 2015 Database Project содержит файл с расширением jfm
  • Должна ли таблица базы данных иметь первичные ключи?
  • Нормализация: что означает «повторяющиеся группы»?
  • Личные имена в глобальном приложении: что хранить
  • Должен ли я иметь выделенное поле первичного ключа?
  • Каково улучшение производительности Sequential Guid по стандартным рекомендациям?
  • Схема для многоязычной базы данных
  • Разница между 3NF и BCNF в простых терминах (должна быть в состоянии объяснить 8-летнему ребенку)
  • Разделить базу данных SQLite между двумя приложениями для Android?
  • Существует ли альтернатива ANSI SQL для ключевого слова MYSQL LIMIT?
  • Interesting Posts

    Как заставить NuGet устанавливать / обновлять все пакеты в файле packages.config?

    android getlastknownlocation возвращает null

    Запрос DynamoDB по дате

    Как открыть одну конкретную папку из галереи в android?

    Самый простой инструмент автоматического анализа и создания диаграмм?

    Есть ли несколько программ для настольных компьютеров для Vista? (Аналогично тому, что включено в GNOME и KDE)

    Как выбрать конкретный параметр в элементе SELECT в jQuery?

    Как конвертировать минуты в часы и минуты (чч: мм) в java

    Как установить папку в качестве виртуального CD / DVD-привода?

    Как я могу запустить действие «Добавить контакт» в android

    Должен ли быть закрыт администратор JPA?

    Как измерить угол наклона телефона в плоскости XY с помощью акселерометра в Android

    Планирование эффективности досрочно и преждевременная оптимизация

    Увеличение размера шрифта в файле справки Windows (CHM)

    Предупреждение о соединении SSL при подключении к базе данных MySQL

    Давайте будем гением компьютера.