Как правильно составить сложный запрос из двух таблиц с сортировкой?

Question

[apptimeru]

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

Есть вот такой запрос:

SELECT g.id, g.title, g.image, s.price, COUNT(s.id) as cnt, SUM(s.orders) as summ FROM store s
LEFT OUTER JOIN games g ON g.id = s.item_id
WHERE g.status = 'publish' AND s.status_item = 'publish' GROUP BY g.id ORDER BY s.id DESC LIMIT 15

Таблица store отвечает за товары, и содержит такие важные поля в этом запросе как orders (количество заказов этого товара), id (для подсчета количества товаров для конкретной игры) и item_id это внешний ключ для соединения с таблицей games.

Таблица games - это таблица с описанием игры, с заголовком, картинкой и другой мета информацией.

Необходимо достать последние игры (последние на основе добавления товаров s.id DESC), и вывести информацию для каждой игры, а именно количество заказов по этой игре и минимальная сумма товара для этой игры. В целом запрос выше справляется с задачей, однако не вытаскивает самую дешевую цену, а достает первую добавленную по ID. Можно было бы добавить MIN(s.price) и тогда он достает что надо, однако так он достает только минимальную цену, но все остальные данные из таблицы store достает не верно (типо цену берет самую низкую, а все остальные данные, такие как есть ли скидка, и другая информация, все равно сортируются по ID).

Абстрактный пример 2х таблиц и необходимый результат:

Comments

[Akina]

Есть вот такой запрос

Запрос некорректен, неполная группировка.

вытаскивает самую дешевую цену, а достает первую добавленную по ID

На самом деле - первую попавшуюся. Что и есть проявление некорректности запроса.

Выкладывайте CREATE TABLE обеих таблиц, пример данных в виде INSERT INTO и требуемый результат для именно этих данных. Плюс укажите точно используемую СУБД, включая и точную её версию.

[apptimeru]

Akina, Прикрепил скриншот, возможно так будет намного понятнее) 10.3.39-MariaDB

[Akina]

10.3.39-MariaDB

Тогда однозначно ROW_NUMBER в CTE.

Answer 1

[Snakecharmer]

Попробуйте например так (но лучше выложите примеры таблиц и данных на fiddle):

SELECT g.id, g.title, g.image, s.price, cnt, summ
FROM games g
LEFT JOIN (
SELECT item_id, MIN(price) as min_price, COUNT(id) as cnt, SUM(orders) as summ
FROM store
WHERE status_item = 'publish'
GROUP BY item_id
) s ON g.id = s.item_id
WHERE g.status = 'publish'
ORDER BY g.id DESC
LIMIT 15

Comments

[apptimeru]

Обновил вопрос и добавил туда скриншот, посмотрите пожалуйста, возможно станет понятнее)

[apptimeru]

Попробовал, цену вытаскивает корректно, однако COUNT(s.id) и SUM(s.orders) перестают работать, так как он вытаскивает только товары с ценной = минимальной)

[Snakecharmer]

apptimeru, обновил ответ.

[apptimeru]

Snakecharmer, Чуток доработал, но в целом все работает, как надо, спасибо большое, очень помогли))

[Snakecharmer]

apptimeru, отлично! Ну, доработать конечно надо было. Без реальных боевых таблиц составить правильный запрос непросто... Абстрактные таблицы лучше не использовать. Лучше потратить чуть больше времени, но показать миниатюрную копию реальных таблиц. Akina сказал в комментариях совершенно верно: нужны CREATE TABLE, INSERT INTO с данными, точная версия СУБД и ожидаемый результат. Выгружать удобно на какой-нибудь фиддл . Успехов!

[apptimeru]

Snakecharmer, оказалось что все же запрос работает не совсем как надо( Из-за MIN(price) в подзапросе, все остальные данные из store вытаскиваются неверные. В таблице store есть другие поля, которые не менее важно достать из этой же строки в которой находится минимальная цена. А так получается что мы достали минимальную цену, а остальные данные например размер скидки (sale), мы получается рандомно не относящиеся к строке с минимальной ценой.

Попытался сменить MIN(price) на ORDER BY price в подзапросе, но из-за группировки это не работает(

[Snakecharmer]

apptimeru, смотрите, вижу что вам посоветовали в Комментариях использовать ROW_NUMBER в CTE. То есть вы пронумеровываете записи и возвращаете первую (или последнюю, как отсортируете), так можно достать минимальную цену.

[apptimeru]

Snakecharmer, я не совсем понимаю как можно использовать ROW_NUMBER в данном случае, ведь сортировка в подзапросе все равно не работает из-за группировки(

Answer 2

[Василий]

надо несколько раз джойний таблицу с инфой о игре минимальная цена 1 джойн. 2 инфа о игре другой