Как ORDER BY в LEFT JOIN в MySQL 5.7
В MySQL 5.7.32 у меня есть две таблицы. Один холдинг продуктов, другой холдинг характеристики с датой связаны. Может быть n количеств характеристик, но меня интересует только та, у которой самая новая дата.
продукты:
ID | NAME
1 | skoda
2 | bmw
содержание:
PROD_ID | TYPE | CONTENT | DATE
1 | color | red | 2020-12-25
1 | color | green | 2020-12-24
1 | size | big | 2020-12-24
2 | color | yellow | 2020-12-24
2 | size | small | 2020-12-24
2 | size | big | 2020-12-20
2 | color | purple | 2020-12-20
1 | color | black | 2020-12-01
Как видите, дизайн служит своего рода журналом изменений, поэтому они не хранятся «обычным» способом.
Теперь самое сложное — получить самый последний контент любого типа для каждого продукта.
ЖЕЛАЕМЫЙ РЕЗУЛЬТАТ:
NAME | COLOR | SIZE
skoda| red | big
audi | yellow| small
Я мог бы сделать простые левые соединения:
SELECT
p.name,
c.content AS color,
c2.content AS size
LEFT JOIN content c ON c.prod_id = p.id WHERE c.type = 'color'
LEFT JOIN content c2 ON c2.prod_id = p.id WHERE c2.type = 'size'
GROUP BY p.id
Есть 2 проблемы:
- Я хочу последний цвет и размер по дате. Как упорядочить по дате в левом соединении?
- Существует множество типов контента. Как это сделать с помощью одного LEFT JOIN и выбрать тип в SELECT?
Есть ли способ в MySQL 5.7 сделать это?
Да, я только что обновился.
Ответы 1
Вы можете сделать это с помощью двух подзапросов:
select p.name
(
select c.content
from content c
where c.prod_id = p.id and c.type = 'color'
order by c.date desc limit 1
) as color,
(
select c.content
from content c
where c.prod_id = p.id and c.type = 'size'
order by c.date desc limit 1
) as size
from product p
Этот запрос будет использовать индекс на content(prod_id, size, date desc, content).
В качестве альтернативы вы можете left join один раз выполнить условную агрегацию:
select p.name,
max(case when c.type = 'color' then c.content end) as color,
max(case when c.type = 'type' then c.content end) as type
from product p
left join content c
on c.prod_id = p.id
and c.date = (
select max(c1.date)
from content c1
where c1.prod_id = c.prod_id and c1.type = c.type
)
group by p.id
Я надеялся на что-то меньшее, например, одно соединение, а затем выбрать цвет в пределах выбора. Запрос уже очень сложный, и этот минимальный пример является его небольшой частью. После того, как группа по всем значениям кажется известной, например. счетчик в столбце контента показывает количество разных цветов, поэтому я надеялся, что можно будет просто выбрать последний.
Ух ты! Спасибо за иллюстрацию альтернативы. Никогда раньше не видел условную агрегацию.
Разрешите еще один вопрос. Альтернатива занимает гораздо больше времени > 2 с вместо 100 мс. Можно ли это решить дополнительными индексами?
@merlin: тот же индекс, который показан в ответе для решения подзапроса, может помочь решению условной агрегации. Но я думаю, что коррелированный подзапрос более эффективен для вашего варианта использования.
Глупый я забыл индекс в столбце даты. Достаточно забавно, что первое решение сократило всего около 100 мс. Я придерживаюсь этого, возвращает все результаты менее чем за 300 мс, достаточно хорошо.
Пожалуйста, опубликуйте также ожидаемый результат.