Блог

Я хочу, чтобы один ко многим присоединился из многих ко многим. Два образца таблиц:

производство:

заказы:

Если я напишу примерный запрос, например

 SELECT ORDERS.ORDER, ORDER.AMOUNT_TO_PRODUCE, SUM(PRODUCTION.PRODUCED_AMOUNT), ORDERS.PRODUCT
FROM ORDERS JOIN PRODUCTION ON ORDERS.PRODUCT_ID = PRODUCTION.PRODUCT_ID
GROUP BY ORDERS.ORDER, ORDER.AMOUNT_TO_PRODUCE, ORDERS.PRODUCT
 

Я получаю

Но я хочу

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

1. суммируйте производственные строки перед их объединением
2. определите первую строку в таблице заказов для каждого заказа и идентификатор продукта, что можно сделать с помощью row_number() аналитической функции
3. соедините оба вместе, что приведет к внешнему соединению суммированных производственных строк с таблицей заказов

Вы можете сделать это вот так:

 WITH production AS (SELECT 'Order1' order_num, 1000 produced_amount, 'ProductID1' product_id FROM dual UNION ALL
                    SELECT 'Order1' order_num, 1000 produced_amount, 'ProductID1' product_id FROM dual UNION ALL
                    SELECT 'Order1' order_num, 5000 produced_amount, 'ProductID2' product_id FROM dual),
         orders AS (SELECT 'Order1' order_num, 600 amount_to_produce, 'Product1 - note1' product, 'ProductID1' product_id FROM dual UNION ALL
                    SELECT 'Order1' order_num, 600 amount_to_produce, 'Product1 - note2' product, 'ProductID1' product_id FROM dual UNION ALL
                    SELECT 'Order1' order_num, 600 amount_to_produce, 'Product1 - note3' product, 'ProductID1' product_id FROM dual UNION ALL
                    SELECT 'Order1' order_num, 5000 amount_to_produce, 'Product2' product, 'ProductID2' product_id FROM dual),
    -- end of mimicking your tables; main query below
    prdctn_amts AS (SELECT order_num,
                           SUM(produced_amount) total_produced_amount,
                           product_id
                    FROM   production
                    GROUP BY order_num,
                             product_id),
     order_dets AS (SELECT order_num,
                           amount_to_produce,
                           product,
                           product_id,
                           row_number () OVER (PARTITION BY order_num, product_id ORDER BY product) rn
                    FROM   orders)
SELECT o.order_num,
       o.amount_to_produce,
       NVL(p.total_produced_amount, 0)
       total_produced_amount,
       o.product
FROM   order_dets o
       LEFT OUTER JOIN prdctn_amts p ON o.order_num = p.order_num
                                        AND o.product_id = p.product_id
                                        AND o.rn = 1
ORDER BY o.order_num,
         o.product_id,
         o.rn;
 

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

 ORDER_NUM AMOUNT_TO_PRODUCE TOTAL_PRODUCED_AMOUNT PRODUCT
--------- ----------------- --------------------- ----------------
Order1                  600                  2000 Product1 - note1
Order1                  600                     0 Product1 - note2
Order1                  600                     0 Product1 - note3
Order1                 5000                  5000 Product2
 

N. B. Вам не понадобились бы первые два подзапроса в предложении with; Я добавил их, чтобы я мог имитировать ваши две таблицы, в то время как вы просто ссылались бы на две таблицы напрямую. Кроме того, как ORDER зарезервированное слово в Oracle, я избежал необходимости использовать " для инкапсуляции имя столбца каждый раз, когда мне нужно было ссылаться на него, изменив имя столбца на ORDER_NUM .