Я пытаюсь создать базу данных (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
), но прямо сейчас я просто не знаю, возможно ли это вообще или мне нужно это сделать. одно из следующего, чтобы (надеюсь) упростить его:
На уровне приложений я использую CakePHP 3.x, но я сомневаюсь, что решение Cake было бы проще, чем решение SQL. Модели-ассоциации кажутся нормальными, все остальное работает как положено.
Наконец, скрипт SQL: http://sqlfiddle.com/#!9/b3d1f8/17
Любая помощь или предложения приветствуются, я не знаю, куда идти ...
Пункт № 1 последняя проверка для CheckType № 2 была на 2017-08-01, Проверка № 3
Сгруппируйте чеки по 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 check_id
не может быть выбран при первом выборе, потому что он не сгруппирован и не агрегирован. Итак, нам нужно выполнить соединение с таблицей checks
, чтобы получить check_id
. - Итак, если это то, что вы искали, примите, пожалуйста, мой ответ. Спасибо.
Извините, мне понятен результат в строке (1 | 2 | 2017-08-01 | 3).