oracle 并排显示两个表中仅匹配部分字段的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11783343/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Display records from two tables side by side matching only some of the fields
提问by user1023993
I have two tables, table A:
我有两张桌子，table A：
Customer_ID Product Date Of Sale Pay Meth 1 Pay Meth 2 QTY
----------- ------- ------------ ---------- ---------- ---
123 AB 1/1/2012 111 222 1
123 AB 1/1/2012 111 222 1
456 AC 2/1/2012 333 444 1
and table B:
和table B：
Customer_ID Product Date Of Sale Pay Meth 1 Pay Meth 2 QTY
----------- ------- ------------ ---------- ---------- ---
123 AB 1/1/2012 111 222 2
456 AB 1/1/2012 124 111 1
I want to match the data up so that the record for customer 123in table Ais grouped as :
我想数据匹配，从而为客户记录123中table A被归纳为：
Customer_ID ?Product ?Date Of Sale ?Pay Meth 1 ?Pay Meth 2 ?QTY
----------- ?------- ?------------ ?---------- ?---------- ?---
123 AB 1/1/2012 111 222 2
and to the right of it appears the following record from table B:
并在它的右侧出现以下记录table B：
Customer_ID ?Product ?Date Of Sale ?Pay Meth 1 ?Pay Meth 2 ?QTY
----------- ?------- ?------------ ?---------- ?---------- ?---
123 AB 1/1/2012 111 222 2
Also (there is always an also) we want to show the third record in table Aand to the right of that record the second record in table B(customer 456) because they Have the same Customer_ID, Productand Date of Sale
另外（总是有一个也）我们想显示第三条记录table A和右边的第二条记录table B（客户456），因为他们有相同的Customer_ID，Product和Date of Sale
So it should look something like
所以它应该看起来像
Customer_ID ?Product ?Date Of Sale ?Pay Meth 1 ?Pay Meth 2 ?QTY Customer_ID ?Product Date Of Sale ?Pay Meth 1 ?Pay Meth 2 ?QTY
----------- ?------- ?------------ ?---------- ?---------- ?---? ----------- ?------- ?------------ ?---------- ?---------- ?---
123 AB 1/1/2012 111 222 1 123 AB 1/1/2012 111 222 1
456 AC 2/1/2012 333 444 1 456 AB 1/1/2012 124 111 1
采纳答案by MakkyNZ
You can do a subquery on each table to get the sum qty for each customer and then join the results on by the customer id e.g
您可以对每个表进行子查询以获取每个客户的总和数量，然后通过客户 ID 加入结果，例如
SELECT a.*, b.*
FROM (
Select customer_id, product, dateofsale, PayMeth1, PayMeth2, SUM(Qty) as Qty
from TableA
Group by customer_id, product, dateofsale, PayMeth1, PayMeth2
) a
JOIN (
Select customer_id, product, dateofsale, PayMeth1, PayMeth2, SUM(Qty) as Qty
from TableB
Group by customer_id, product, dateofsale, PayMeth1, PayMeth2
) b
ON a.customer_id = b.customer_id
回答by Alex Kalicki
What you're looking for is the SQL JOIN command: http://www.tizag.com/sqlTutorial/sqljoin.php
您正在寻找的是 SQL JOIN 命令：http: //www.tizag.com/sqlTutorial/sqljoin.php
You'd want to join the two tables only for rows where the column(s) you want to match do so. So if you're trying to match records from the two tables based on customer_id then your SQL code would look something like this:
您只想为要匹配的列的行连接两个表。因此，如果您尝试根据 customer_id 匹配两个表中的记录，那么您的 SQL 代码将如下所示：
SELECT *
FROM A
JOIN B
ON A.Customer_ID = B.Customer_ID
Not sure what you mean when you say those 2 records have the same ID, Product, and Date of Sale though - they seem to only have an ID (456) in common.
当您说这两条记录具有相同的 ID、产品和销售日期时，不确定您的意思 - 它们似乎只有一个共同的 ID (456)。
