问题描述
假设我有两张桌子:
Requested_Products
------------------------------------------
orderId| productId|productDesc |prodQty
------------------------------------------
order1 | product1 | description1 | 1
order2 | product2 | description2 | 2
order2 | product3 | description3 | 5
order2 | product4 | description4 | 6
和
Used_Materials
-------------------------------------------
orderId| materialId| materialDesc |matQty
-------------------------------------------
order1 | material1 | description4 | 3
order1 | material2 | description5 | 6
order1 | material3 | description6 | 2
order2 | material4 | description7 | 8
如何通过 orderId 合并这些表以获得这样的表:
How can merge those tables by orderId to get table like this:
---------------------------------------------------------------------------------
orderId| productId |productDesc | prodQty | materialId| materialDesc |matQty
---------------------------------------------------------------------------------
order1 | product1 | description1 | 1 | material1 | description4 | 3
order1 | null | null | null | material2 | description5 | 6
order1 | null | null | null | material3 | description6 | 2
order2 | product2 | description2 | 2 | material4 | description7 | 8
order2 | product3 | description3 | 5 | null | null | null
order2 | product4 | description4 | 6 | null | null | null
我想要做的是创建包含请求的项目和用于订购的材料的单个表格.我需要这个表格用于报告目的.
What i am trying to do is to create single table with requested items and used materials for order. I need this table for reporting purposes.
我不能使用 JOIN，因为 Requested_Products 和 Used_Materials 中的行不能重复
I cant use JOINs because rows from Requested_Products and Used_Materials cant dublicate
感谢帮助
推荐答案
我想我明白你在问什么.一个完整的外部不会给你你正在寻找的结果，至少在你的问题中是这样描述的.以这种方式查询结果似乎有点不典型，通常我会说在 2 个查询中提取数据，并单独显示在您的工具中，因为您的结果中没有显示真正的关系.
I think I understand what you are asking. A FULL OUTER alone won't give you the results you are looking for, at least as described in your question. It seems a little atypical to query results this way, and normally I would say to pull the data in 2 queries, and display in your tool separately as there is no real relationship being displayed in your result.
但是为了回答问题...
However in the interest of answering the question...
CREATE TABLE Requested_Products (
orderId VARCHAR(128)
,productId VARCHAR(128)
,productDesc VARCHAR(128)
,prodQty NUMBER(18)
);
COPY Requested_Products FROM STDIN DELIMITER '|';
order1|product1|description1|1
order2|product2|description2|2
order2|product3|description3|5
order2|product4|description4|6
\.
CREATE TABLE Used_Materials (
orderId VARCHAR(128)
,materialId VARCHAR(128)
,materialDesc VARCHAR(128)
,matQty NUMBER(18)
);
COPY Used_Materials FROM STDIN DELIMITER '|';
order1|material1|description4|3
order1|material2|description5|6
order1|material3|description6|2
order2|material4|description7|8
\.
\pset null null
SELECT COALESCE(RP.orderId, UM.orderId) AS orderId, productId, productDesc, prodQty, materialId, materialDesc, matQty
FROM ( SELECT X.*, ROW_NUMBER() OVER (PARTITION BY OrderId) Order_Product_Number FROM Requested_Products X ) AS RP
FULL OUTER JOIN
( SELECT X.*, ROW_NUMBER() OVER (PARTITION BY OrderId) Order_Material_Number FROM Used_Materials X ) AS UM
ON ( RP.orderId = UM.orderId AND RP.Order_Product_Number = UM.Order_Material_Number );
orderId | productId | productDesc | prodQty | materialId | materialDesc | matQty
---------+-----------+--------------+---------+------------+--------------+--------
order1 | product1 | description1 | 1 | material1 | description4 | 3
order1 | null | null | null | material2 | description5 | 6
order1 | null | null | null | material3 | description6 | 2
order2 | product2 | description2 | 2 | material4 | description7 | 8
order2 | product3 | description3 | 5 | null | null | null
order2 | product4 | description4 | 6 | null | null | null
(6 rows)
至于什么和为什么...您必须使用 FULL OUTER 来确保连接两侧的所有行都被拉入.不过，您不想要扇出.为此，我们将使用由 orderId 分区的 ROW_NUMBER() 生成的序列排列数据.这将创建一个在 orderId 更改时重置的数字，以便我们可以加入它.您可能决定要以某种方式对序列进行排序，您可以在 partition by 子句中做到这一点.最后，因为任一表的 orderId 可能为空，并且您使用的是 FULL OUTER，所以您必须 COALESCE 以确保始终显示非空值.
As for what and why... You have to use FULL OUTER to ensure all rows on either side of the join are pulled in. You don't want a fanout, though. To do this, we will line up the data using a sequence generated by the ROW_NUMBER() partitioned by orderId. This will create a number that resets on change of orderId so we can join on it. You may decide you want to order the sequences a certain way, and you can do that in the partition by clause. Finally, because orderId may be null for either table and you are using a FULL OUTER, you must COALESCE to ensure you always show a non-null value.
这可能效率不高.我确实觉得我在给你一把霰弹枪，并为你瞄准你的脚.我只需要假设你知道你在做什么，换个角度看，然后把我的手指放在我的耳朵里.
This probably won't be efficient. I do feel like I'm giving you a shotgun and aiming it at your foot for you. I'll just have to assume you know what you're doing, look the other way, and put my finger in my ears.
这篇关于按列合并两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！