Самостоятельное присоединение возвращает только одну запись

Работаем над системой управления запасами, и у нас есть следующие таблицы:

================================================
| orders | order_line_items  | product_options | 
|--------|-------------------|-----------------|
| id     | id                | id              |
| start  | order_id          | name            |
| end    | product_option_id |                 |
|        | quantity          |                 |
|        | price             |                 |
|        | event_start       |                 |
|        | event_end         |                 |
================================================

Я пытаюсь рассчитать запасы на определенную дату, поэтому мне нужно выполнить самообъединение, чтобы сравнить количество в order_line_items с суммой количества других записей в order_line_items с тем же product_option_id, и где начало и конец события находятся в пределах диапазона.

Итак, учитывая дату 2016-01-20, у меня есть:

SELECT order_line_items.id, order_line_items.product_option_id, order_line_items.order_id FROM order_line_items
WHERE order_line_items.event_end_date >= '2016-01-20 04:00:00'
AND order_line_items.event_start_date <= '2016-01-21 04:00:00'
AND order_line_items.product_option_id IS NOT NULL;

Вышеприведенное возвращает 127 строк

Когда я пытаюсь сделать самосоединение, например:

SELECT 
order_line_items.id, 
order_line_items.product_option_id, 
order_line_items.order_id, 
order_line_items.quantity, 

other_line_items.other_product_option_id, 
other_line_items.other_order_id, 
other_line_items.other_quantity, 
other_line_items.total 

FROM order_line_items

JOIN (
    SELECT 
        id, 
        product_option_id AS other_product_option_id, 
        order_id AS other_order_id, 
        quantity AS other_quantity, 
        SUM(quantity) total
    FROM order_line_items
    WHERE order_line_items.event_end_date >= '2016-01-20 04:00:00'
    AND order_line_items.event_start_date <= '2016-01-21 04:00:00'
) other_line_items ON order_line_items.product_option_id = other_line_items.other_product_option_id

WHERE order_line_items.event_end_date >= '2016-01-20 04:00:00'
AND order_line_items.event_start_date <= '2016-01-21 04:00:00'
AND order_line_items.product_option_id IS NOT NULL;

Он возвращает только 1 запись. Как вы можете видеть здесь: (https://goo.gl/BhUYxK) существует множество записей с одинаковым product_option_id, поэтому этот последний запрос должно возвращать много строк


mysql join
person Laravelian    schedule 20.01.2016    source источник
comment
Предложение, нужно переименовать вторую таблицу, потому что система может запутаться, и не нужно вторую, где, потому что у вас есть один в подзапросе.   -  person Carlos Rodriguez    schedule 21.01.2016
comment
Спасибо - удаление второго, где я получил больше записей, но теперь все возвращаемые записи имеют один и тот же product_option_id .. странно: goo .gl/itL5bF   -  person Laravelian    schedule 21.01.2016
comment
хорошо, попробуй это в варианте join, order_id = other_order_id and production_order_id = other_production_order_id   -  person Carlos Rodriguez    schedule 21.01.2016
comment
Хм, но мне нужно присоединиться к product_option_id, потому что я пытаюсь получить общее количество всех других записей, которые соответствуют product_option_id в запросе - не имеет ничего общего с order_ids - поэтому у меня есть x количество заказанных на один строка для Chair ProductOption -- мне нужно знать из всех других записей, которые соответствуют запросу, каково общее количество заказанных стульев   -  person Laravelian    schedule 22.01.2016

Ответы (4)


arrow_upward
2
arrow_downward

Добавленный SUM(...) превращает подзапрос в одну строку. Возможно, подзапрос должен был иметь один из них:

GROUP BY (id)
GROUP BY (product_option_id)
GROUP BY (order_id)

(Я недостаточно хорошо знаю схему или приложение, чтобы сказать, что имеет смысл.)

(Пожалуйста, используйте более короткие и отличительные псевдонимы; SQL очень трудно читать из-за длины и сходства order_line_items и other_line_items.)

person Rick James    schedule 25.01.2016

arrow_upward
1
arrow_downward

Вы действительно хотите получить следующее? :

SELECT product_option_id, sum(quantity)
FROM order_line_items
WHERE event_end_date   >= '2016-01-20 04:00:00'
AND   event_start_date <= '2016-01-21 04:00:00'
GROUP BY 1

Я не могу сказать, зачем вам нужно самостоятельное соединение здесь

person SIDU    schedule 27.01.2016

arrow_upward
0
arrow_downward

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

SELECT oli.order_id, 
       oli.product_option_id,
       oli.quantity,
       po.total_quantity   
  FROM order_line_items oli
  JOIN (
    SELECT product_option_id,
           SUM(quantity) total_quantity
      FROM order_line_items
     WHERE event_end   >= '2016-01-20 04:00:00'
       AND event_start <= '2016-01-21 04:00:00'
  GROUP BY product_option_id
       ) po
    ON po.product_option_id = oli.product_option_id
 WHERE oli.event_end   >= '2016-01-20 04:00:00'
   AND oli.event_start <= '2016-01-21 04:00:00'

Если у вас может быть несколько строк одного и того же product_option в заказе, вам может потребоваться настроить это следующим образом:

  SELECT oli.order_id, 
         oli.product_option_id,
         SUM(oli.quantity) order_quantity,
         po.total_quantity   
    FROM order_line_items oli
    JOIN (
      SELECT product_option_id,
             SUM(quantity) total_quantity
        FROM order_line_items
       WHERE event_end   >= '2016-01-20 04:00:00'
         AND event_start <= '2016-01-21 04:00:00'
    GROUP BY product_option_id
         ) po
      ON po.product_option_id = oli.product_option_id
   WHERE oli.event_end   >= '2016-01-20 04:00:00'
     AND oli.event_start <= '2016-01-21 04:00:00'
GROUP BY oli.order_id, 
         oli.product_option_id
person Arth    schedule 28.01.2016

arrow_upward
0
arrow_downward

Насколько я понимаю, вам на самом деле следует присоединиться к orders и product_options к таблице order_line_items.

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

SELECT product_options.id, production_options.name, SUM(order_line_items.quantity)
FROM order_line_items
LEFT JOIN product_options ON production_options.id=order_line_items.product_option_id
LEFT JOIN orders ON orders.id=order_line_items.order_id
WHERE orders.start>='SOME DATETIME' AND orders.end<='SOME DATETIME'
GROUP BY product_options.id

Кроме того, просто комментарий, product_options, вероятно, следует просто назвать products. Короткие названия столов для победы!

person fiddlestacks    schedule 31.01.2016
comment
Я просто больше думал о хороших именах таблиц. Я бы выбрал order, item и order_item. - person fiddlestacks; 31.01.2016