MySQL: получить каждую последнюю запись из объединенной таблицы

Я пытаюсь создать базу данных (SQLFiddle ниже) для Items (пусть это будет любая машина, в этом примере автомобили и велосипеды), которую вероятно необходимо проверять с заданным интервалом для одного или нескольких CheckTypes.

Item имеет много ItemsCheckTypes:

  • Item Car1 необходимо проверять каждые 2 года в гараже и ежегодно визуально.
  • Item Car2 нужно только проверить в гараже
  • Item Bike2 вообще не нуждается в проверке.

Check принадлежит к CheckType и имеет много ItemsChecks:

  • Check 01.01.2018 с CheckType «Осмотр гаража», Car1 и Car2 были проверены (ItemsChecks выполняет задание)

Теперь мне нужен запрос, чтобы узнать последний раз, Item был проверен на каждый его ItemsCheckTypes. Ожидается такой результат:

Items.id | CheckTypes.id | LAST CHECK.date | LAST CHECK.id
1        | 1             | 2018-01-01      | 4
1        | 2             | 2017-08-01      | 3
2        | 1             | 2016-01-01      | 1

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

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

На уровне приложений я использую CakePHP 3.x, но я сомневаюсь, что решение Cake было бы проще, чем решение SQL. Модели-ассоциации кажутся нормальными, все остальное работает как положено.

Наконец, скрипт SQL: http://sqlfiddle.com/#!9/b3d1f8/17

Любая помощь или предложения приветствуются, я не знаю, куда идти ...

Извините, мне понятен результат в строке (1 | 2 | 2017-08-01 | 3).

Tính Ngô Quang 10.08.2018 11:11

Пункт № 1 последняя проверка для CheckType № 2 была на 2017-08-01, Проверка № 3

nappo 10.08.2018 11:19
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
2
2
73
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Сгруппируйте чеки по item_id и check_type_id и получите самую последнюю дату:

select
    ic.item_id, 
    c.check_type_id, 
    max(c.date) latest
from items_checks ic
inner join checks c on c.id = ic.check_id
group by 
    ic.item_id, 
    c.check_type_id
;

check_id по-прежнему отсутствует. Итак, мы берем предыдущий выбор и объединяем его с таблицей checks, чтобы получить его.

select
    c2.item_id,
    c2.check_type_id,
    c2.latest last_check,
    c1.id check_id
from checks c1
inner join (
    select ic.item_id, c.check_type_id, max(c.date) latest
    from items_checks ic
    inner join checks c on c.id = ic.check_id
    group by ic.item_id, c.check_type_id
) c2 on c2.check_type_id = c1.check_type_id and c2.latest = c1.date
order by
    c2.item_id,
    c2.check_type_id
;

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

nappo 10.08.2018 11:27

@nappo check_id не может быть выбран при первом выборе, потому что он не сгруппирован и не агрегирован. Итак, нам нужно выполнить соединение с таблицей checks, чтобы получить check_id. - Итак, если это то, что вы искали, примите, пожалуйста, мой ответ. Спасибо.

Henrik 10.08.2018 11:35

Другие вопросы по теме