Как правильно составить сложный запрос из двух таблиц с сортировкой?
Здравствуйте уважаемые эксперты, не могу решить данную головоломку уже месяц, пробовал разные варианты, однако все работает не совсем как надо.
Есть вот такой запрос:
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 |
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х таблиц и необходимый результат:
Дополнительно:
Есть вот такой запрос
Запрос некорректен, неполная группировка.
вытаскивает самую дешевую цену, а достает первую добавленную по ID
На самом деле - первую попавшуюся. Что и есть проявление некорректности запроса.
Выкладывайте CREATE TABLE обеих таблиц, пример данных в виде INSERT INTO и требуемый результат для именно этих данных. Плюс укажите точно используемую СУБД, включая и точную её версию.
10.3.39-MariaDB
Тогда однозначно ROW_NUMBER в CTE.
Попробуйте например так (но лучше выложите примеры таблиц и данных на 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 |
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
- Обновил вопрос и добавил туда скриншот, посмотрите пожалуйста, возможно станет понятнее)
- Попробовал, цену вытаскивает корректно, однако COUNT(s.id) и SUM(s.orders) перестают работать, так как он вытаскивает только товары с ценной = минимальной)
- apptimeru, обновил ответ.
- Snakecharmer, Чуток доработал, но в целом все работает, как надо, спасибо большое, очень помогли))
- apptimeru, отлично! Ну, доработать конечно надо было. Без реальных боевых таблиц составить правильный запрос непросто... Абстрактные таблицы лучше не использовать. Лучше потратить чуть больше времени, но показать миниатюрную копию реальных таблиц. Akina сказал в комментариях совершенно верно: нужны CREATE TABLE, INSERT INTO с данными, точная версия СУБД и ожидаемый результат. Выгружать удобно на какой-нибудь фиддл . Успехов!
- Snakecharmer, оказалось что все же запрос работает не совсем как надо( Из-за MIN(price) в подзапросе, все остальные данные из store вытаскиваются неверные. В таблице store есть другие поля, которые не менее важно достать из этой же строки в которой находится минимальная цена. А так получается что мы достали минимальную цену, а остальные данные например размер скидки (sale), мы получается рандомно не относящиеся к строке с минимальной ценой.
Попытался сменить MIN(price) на ORDER BY price в подзапросе, но из-за группировки это не работает(
- apptimeru, смотрите, вижу что вам посоветовали в Комментариях использовать ROW_NUMBER в CTE. То есть вы пронумеровываете записи и возвращаете первую (или последнюю, как отсортируете), так можно достать минимальную цену.
- Snakecharmer, я не совсем понимаю как можно использовать ROW_NUMBER в данном случае, ведь сортировка в подзапросе все равно не работает из-за группировки(
Ответы:
надо несколько раз джойний таблицу с инфой о игре минимальная цена 1 джойн. 2 инфа о игре другой
Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.
Пока нет других ответов. Будьте первым, кто поможет автору.
Ответить на вопрос
Для составления сложного запроса из двух таблиц с сортировкой в языке SQL, вам потребуется использовать оператор JOIN для объединения таблиц и оператор ORDER BY для сортировки результатов. Ниже приведен пример запроса на языке SQL (например, MySQL) с объединением таблиц и сортировкой:
SELECT table1.column1, table1.column2, table2.column1 FROM table1 JOIN table2 ON table1.common_column = table2.common_column ORDER BY table1.column1 ASC, table2.column1 DESC;
В данном запросе:
- Выбираются столбцы column1 и column2 из таблицы table1 и столбец column1 из таблицы table2.
- Используется оператор JOIN для объединения таблиц table1 и table2 по общему столбцу common_column.
- Оператор ORDER BY используется для сортировки результатов. В данном случае, данные сортируются по возрастанию столбца column1 из таблицы table1 и по убыванию столбца column1 из таблицы table2.
Помните, что для корректной работы запроса необходимо заменить table1, table2, column1, column2, common_column на реальные названия таблиц и столбцов из вашей базы данных.
Таким образом, вы можете составить сложный запрос из двух таблиц с сортировкой, используя оператор JOIN и оператор ORDER BY в языке SQL.