并排显示两个表中的记录，仅匹配某些字段 [英] Display records from two tables side by side matching only some of the fields

问题描述

我有两个表，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

和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

我想对数据进行匹配，以便将table A中的客户123的记录分组为:

I want to match the data up so that the record for customer 123 in table A is grouped as :

Customer_ID  Product  Date Of Sale  Pay Meth 1  Pay Meth 2  QTY
-----------  -------  ------------  ----------  ----------  ---
        123  AB       1/1/2012             111         222    2

并在其右侧显示table B中的以下记录:

and to the right of it appears the following record from table B:

Customer_ID  Product  Date Of Sale  Pay Meth 1  Pay Meth 2  QTY
-----------  -------  ------------  ----------  ----------  ---
        123  AB       1/1/2012             111         222    2

我们还希望(总是有一个)在table A中显示第三条记录，并在该记录的右侧显示table B(客户456)中的第二条记录，因为它们具有相同的Customer_ID ，Product和Date of Sale

Also (there is always an also) we want to show the third record in table A and to the right of that record the second record in table B (customer 456) because they Have the same Customer_ID, Product and Date of Sale

所以它应该看起来像

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

推荐答案

您可以在每个表上执行子查询，以获取每个客户的总和，然后按客户ID将结果加入 例如

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

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

这篇关于并排显示两个表中的记录，仅匹配某些字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！