Oracle进行一对一连接

Question

我想从多对多进行一对多连接。两个表示例:

生产:

10005000

PRODUCED_AMOUNT	PRODUCT_ID
Order1	1000	ProductID1
ProductID1	ProductID2

看起来您想要查找生产的总数量，并将其连接到订单表中每个订单和产品id的第一行。如果是这种情况，您应该:

1. 在加入生产行之前将它们相加
2. 根据订单和产品id识别订单表中的第一行，这可以通过使用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

注意:在with子句中不需要前两个子查询;我添加它们是为了模拟您的两个表，而您只需要直接引用这两个表。另外，由于ORDER在Oracle中是一个保留字，因此每次需要引用列名时，我都可以通过将列名更改为ORDER_NUM来避免使用"来封装列名。