并排显示两个表中的记录，仅匹配某些字段

小编典典

并排显示两个表中的记录，仅匹配某些字段

sql

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

我想数据匹配，从而为客户记录123中table A被归纳为：

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

并在其右侧显示来自的以下记录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

所以它看起来应该像

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

阅读 159

2021-05-23

共1个答案

小编典典

您可以在每个表上执行子查询以获取每个客户的总和，然后按客户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

2021-05-23