Как написать собственный запрос на выборку MySQL для заказов и их элементов

Я сделал все, что мог, чтобы описать свою проблему как можно подробнее; если есть вопросы, прокомментируйте и задавайте. Я понимаю, что, возможно, упустил очень простое решение, и мне интересно услышать, как я могу сделать это проще; При этом убедитесь, что ваш ответ учитывает все, что я описал, и вы задали все вопросы (и получили ответы), прежде чем публиковать свое решение.

Задний план

Две таблицы: orders и items, каждая из которых содержит уникальную информацию (они используют идентификаторы для связи друг с другом; повторяющиеся данные отсутствуют). См. Приложения A и B (ниже).

Таблица orders (Приложение A) содержит всю информацию, уникальную для этого заказа. Каждый товар, добавленный в заказ, имеет свою собственную строку в таблице, содержащую идентификатор заказа, идентификатор товара и количество (и другие нерелевантные поля).

Проблема

Я создаю интерфейс, который позволит пользователю фильтровать заказы по следующим параметрам:

  • количество позиций (товар)
  • количество штук (количество товаров)
  • Стоимость (общая стоимость заказа: количество товаров, умноженное на стоимость товара)

Мне кажется, что можно собрать все данные в один MySQL-запрос. Вот информация, которую мне нужно собрать:

  • количество позиций в заказе
  • идентификаторы предметов
  • количество купленных (на продукт)

Пример

Пользователь хочет отфильтровать все заказы по следующим параметрам:

  • три предмета (продукты)
  • семь штук (общее количество)
  • менее 600 долларов

Если бы все данные (заказ и элемент) содержались в одной таблице, запрос был бы примерно таким:

SELECT order_ID, 
       GROUP_CONCAT(item_ID) as item_IDs, 
       GROUP_CONCAT(quantity) as quantities, 
       SUM(price) as price 
FROM orders 
GROUP BY order_ID 
HAVING 7 = SUM(quantity) AND 
       3 = COUNT(*)

и верните что-то вроде этого:

order_ID | item_IDs | quantities | price
=========================================
15       | 22,9,36  | 1,4,2      | $582

Но поскольку цена хранится в отдельной таблице, все сложнее. Я создал этот запрос (см. Приложение C):

SELECT DISTINCT o.order_ID, 
       GROUP_CONCAT(o.`item_ID`) as item_IDs,
       GROUP_CONCAT(o.`quantity`) as quantities 
FROM orders o 
GROUP BY o.`order_ID` 
HAVING 7 = SUM(o.`quantity`) AND 
       3 = COUNT(*) 
ORDER BY o.`order_ID` DESC`

что приближает меня, но оставляет желать лучшего. Я пробовал использовать JOINS (В чем разница между INNER JOIN, LEFT JOIN, RIGHT JOIN и FULL JOIN?), но в нерабочее время не удалось заставить что-либо работать.

Вопрос

Можно ли получить все эти данные в одном запросе MySQL и / или есть лучший способ, чем путь, по которому я сейчас иду (что бы вы сделали)?

Было бы приемлемо, если бы запрос возвращал результаты, подобные этому:

order_ID | item_ID | quantity | price
======================================
177      | 42      | 2        | 50.00
177      | 45      | 3        | 30.00
177      | 46      | 2        | 10.00
150      | 39      | 3        | 25.00
150      | 47      | 1        | 95.00
150      | 41      | 3        | 15.00

при условии, что запрос требовал, чтобы итоговые заказы состояли из трех и семи частей, а их общая сумма составляла менее 600 долларов США (в соответствии с приведенным примером).

Я также смирился с тем, что это может быть невозможно в рамках одного запроса, и я знаю, как сделать это с помощью PHP; подумал, что могу попробовать сделать большую часть этого в MySQL, насколько это возможно, и кое-чему научусь сегодня. :)

Некоторые из вас, вероятно, предложат изменить структуру таблицы, но я бы предпочел не иметь таблиц с повторяющимися данными, если это можно сделать: если цена продукта изменится, мне бы не пришлось обновлять за два таблицы.

Спасибо за ваше время и любые решения, которые у вас могут быть; Я очень благодарен вам за любую помощь.

Экспонаты / наглядные пособия

Приложение A: orders Таблица

ID    | order_ID | item_ID | quantity
======================================
1146  | 195      | 52      | 3
1145  | 195      | 1       | 4
1142  | 193      | 41      | 1
1141  | 193      | 40      | 3
1031  | 177      | 45      | 3
1032  | 177      | 46      | 2
1030  | 177      | 42      | 2
881   | 150      | 47      | 1
880   | 150      | 39      | 3
882   | 150      | 41      | 3

Приложение B: items Таблица

(предоставлено для полноты, не критичной информации) https://cldup.com/ZQEDy-vef6.png

Приложение C: запрос и результаты

SELECT DISTINCT o.order_ID, 
       GROUP_CONCAT(o.`item_ID`) as item_IDs,
       GROUP_CONCAT(o.`quantity`) as quantities 
FROM orders o 
GROUP BY o.`order_ID` 
HAVING 7 = SUM(o.`quantity`) AND 
       3 = COUNT(*) 
ORDER BY o.`order_ID` DESC

.

order_ID | item_IDs | quantities
=================================
177      | 42,45,46 | 2,3,2
150      | 39,47,41 | 3,1,3
115      | 39,47,41 | 3,1,3
108      | 48,45,46 | 2,3,2

Приложение D: SQLFiddle

http://sqlfiddle.com/#!9/2763f5


mysql join count sum having
person Caleb    schedule 26.10.2015    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Я начну здесь, и я закончил его шаг за шагом.

Во-первых, LEFT JOIN - ваш друг. Таким образом, вы можете узнать цену каждой позиции

Пример 1:

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

SELECT o.order_ID, 
       o.item_ID as item_ID,
       o.quantity as quantitie,
       i.price
FROM orders o
LEFT JOIN items i ON o.item_ID = i.id
ORDER BY o.order_ID
LIMI 100;

Пример 2:

Теперь ваш запрос должен разделиться на 2 части. Сначала мы получим все идентификаторы заказов, которые соответствуют вашим критериям (позиции, единицы и цена).

     SELECT o.order_ID
        FROM orders o
        LEFT JOIN items i ON o.item_ID = i.id
        GROUP BY o.order_ID
        HAVING
         SUM(o.`quantity`) =7 
       AND
         SUM(1) = 3
       AND
         SUM(i.price) < 700;
       ORDER BY o.order_ID;

Пример 3:

Теперь у него есть все order_ids, и он может комбинировать два запроса.

SELECT o.order_ID, 
       o.item_ID as item_ID,
       o.quantity as quantitie,
       i.price
FROM orders o
LEFT JOIN items i ON o.item_ID = i.id
where o.order_ID IN (
    SELECT o.order_ID
    FROM orders o
    LEFT JOIN items i ON o.item_ID = i.id
    GROUP BY o.order_ID
    HAVING
     SUM(o.`quantity`) =7 
   AND
     SUM(1) = 3
   AND
     SUM(i.price) < 700    )
ORDER BY o.order_ID, o.quantity, i.price;

Но это не тест. Если вы поместите примерную дату через sqlfidle, я могу напрямую протестировать ее для вас.

Пример 4

Я изменил последний запрос на LEFT JOINS, но он неправильно работает со скрипкой. У меня тайм-аут. Пожалуйста, проверьте это на своем сервере.

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

  SELECT mo.order_ID, 
        mo.item_ID ,
        mo.quantity,
        mi.price
   FROM (
        SELECT o.order_ID
        FROM orders o
        LEFT JOIN items i ON o.item_ID = i.id
        GROUP BY o.order_ID
        HAVING
         SUM(o.`quantity`) =7 
       AND
         SUM(1) = 3
       AND
         SUM(i.price) < 700 
    ) AS ord
    LEFT JOIN orders mo ON ord.order_ID = mo.order_ID
    LEFT JOIN items  mi ON mo.item_ID = mi.id
    ORDER BY ord.order_id, mo.quantity, mi.price;

Результат

+----------+---------+----------+--------+
| order_ID | item_ID | quantity | price  |
+----------+---------+----------+--------+
|      115 |      47 |        1 |  30.00 |
|      115 |      41 |        3 |  42.00 |
|      115 |      39 |        3 |  60.00 |
|      150 |      47 |        1 |  30.00 |
|      150 |      41 |        3 |  42.00 |
|      150 |      39 |        3 |  60.00 |
|      177 |      46 |        2 | 120.00 |
|      177 |      42 |        2 | 120.00 |
|      177 |      45 |        3 | 180.00 |
+----------+---------+----------+--------+
9 rows in set (0.06 sec)
person Bernd Buffen    schedule 26.10.2015
comment
вау, быстрый ответ; Спасибо! Я добавил SQLFiddle как Exhibit D. из того, что я прочитал, подзапросы не являются отличным вариантом для производительности ... но я предполагаю, поскольку это то, что вы ответили, невозможно получить предпочтительный результат без подзапроса ? - person Caleb; 27.10.2015
comment
У меня есть testet Sample 3, и результат мне кажется хорошим. Вы также можете сделать это с помощью дополнительных ЛЕВЫХ СОЕДИНЕНИЙ. Я сделаю образец - person Bernd Buffen; 27.10.2015
comment
Я также тестировал с полной базой данных, и результат казался идеальным! Большое спасибо за ваше работающее решение. Мне любопытно узнать, можно ли обойтись без подзапроса. - person Caleb; 27.10.2015
comment
@Caleb Я добавил образец 4 - person Bernd Buffen; 27.10.2015
comment
при выполнении Пример 4: Ошибка MySQL № 1248 - Каждая производная таблица должна иметь собственный псевдоним - person Caleb; 27.10.2015
comment
спасибо за подсказку об индексах ... Я добавил простой ключ к столбцу order_ID, и это * радикально сократило время запроса. - person Caleb; 27.10.2015
comment
oooof, поэтому я только что заметил, что запрос не принимает количество и не умножает его на цену при рассмотрении фильтра по стоимости. в настоящее время запрос суммирует стоимость каждого отдельного продукта, а не количество заказанного продукта. это легко поправимо? кажется простой оплошностью, но нелегко исправить ...? - person Caleb; 28.10.2015
comment
ВЫБРАТЬ mo.order_ID, mo.item_ID, mo.quantity, mo.quantity * mi.price изменить в начале запроса - person Bernd Buffen; 28.10.2015
comment
Спасибо за быстрый ответ, @Bernd. это определенно возвращает правильную цену, но условие HAVING SUM(i.price) < 700 не учитывает количество купленного продукта. - person Caleb; 28.10.2015
comment
ты прав. Я дам образец сегодня вечером ИЛИ тестирую SIM-карту (o.quantity * i.price) ‹700 @Caleb - person Bernd Buffen; 28.10.2015
comment
Я попробовал, и, похоже, это сработало; не могу поверить, что это так просто. - person Caleb; 29.10.2015