我想從多對多進行一對多連接。兩個表示例：
生產：
| 命令 | PRODUCED_AMOUNT | 產品編號 |
|---|---|---|
| 訂單1 | 1000 | 產品ID1 |
| 訂單1 | 1000 | 產品ID1 |
| 訂單1 | 5000 | 產品ID2 |
訂單：
| 命令 | AMOUNT_TO_PRODUCE | 產品 | 產品編號 |
|---|---|---|---|
| 訂單1 | 600 | 產品 1 - 注 1 | 產品ID1 |
| 訂單1 | 600 | 產品 1 - 注 2 | 產品ID1 |
| 訂單1 | 600 | 產品 1 - 注 3 | 產品ID1 |
| 訂單1 | 5000 | 產品2 | 產品ID2 |
如果我寫一個示例查詢，如
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
我得到
| 命令 | AMOUNT_TO_PRODUCE | PRODUCED_AMOUNT | 產品 |
|---|---|---|---|
| 訂單1 | 600 | 2000年 | 產品 1 - 注 1 |
| 訂單1 | 600 | 2000年 | 產品 1 - 注 2 |
| 訂單1 | 600 | 2000年 | 產品 1 - 注 3 |
| 訂單1 | 5000 | 5000 | 產品2 |
但我想要
| 命令 | AMOUNT_TO_PRODUCE | PRODUCED_AMOUNT | 產品 |
|---|---|---|---|
| 訂單1 | 600 | 2000年 | 產品 1 - 注 1 |
| 訂單1 | 600 | 0 | 產品 1 - 注 2 |
| 訂單1 | 600 | 0 | 產品 1 - 注 3 |
| 訂單1 | 5000 | 5000 | 產品2 |
uj5u.com熱心網友回復：
看起來您想要查找生產的總金額并將其連接到每個訂單和產品 ID 的訂單表中的第一行。如果是這種情況，您應該：
- 在加入生產行之前對它們求和
- 確定每個訂單和產品 ID 的訂單表中的第一行，您可以使用
row_number()分析函式來完成 - 將兩者連接在一起，這將涉及匯總的生產行到訂單表的外部連接
你可以這樣做：
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
注意您不需要 with 子句中的前兩個子查詢；我已經添加了它們，所以我可以模仿你的兩個表，而你只需要直接參考這兩個表。此外，作為ORDEROracle 中的保留字，我"通過將列名更改為ORDER_NUM.
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/337166.html