时间戳

首页 > 解决方案 > 如何实现连接以合并两个表 FULL OUTER JOIN 并避免重复？

sql - 如何实现连接以合并两个表 FULL OUTER JOIN 并避免重复？

问题描述

我正在尝试加入TABLE A新TABLE B表。我想避免重复，但我想保留表的所有记录。

我已经尝试过完全外连接，左外连接，但我无法达到预期的结果。

TABLE A
======================
CLIENT_ID   SUPPLIER_NO  INVOICE_COUNT VOLUME
ABC         1234         10            456
GEF             4567         20        657      
ERT         8912         30        567
THE         6872         42        781

TABLE B
========================
CLIENT_ID   SUPPLIER_NO REJECTED_COUNT  REJECTED_VOLUME
ERT     8912        56          102
HJI     7823        34          781
REW     6721        45          632

FINAL TABLE(WHAT IT SHOULD LOOK LIKE)
========================
CLIENT_ID SUPPLIER_NO INVOICE_COUNT VOLUME REJECTED_COUNT REJECTED_VOLUME
ABC   1234        10         456   NULL or 0      NULL or 0 
GEF   4567        20         657   NULL or 0      NULL or 0     
ERT   8912        30         567   56         102
THE   6872        42         781   NULL or 0      NULL or 0     
HJI   7823        NULL       NULL  34         781
REW   6721        NULL       NULL  45         632

我的查询是

select   A.client_id
      ,  A.supplier_no
      ,  count(*)        as Rejected_Count 
      , sum(TOTAL_AMT)   as Rejected_Volume
      , count(*)         as InvoiceCount 
      , sum(INVOICE_AMT) as Volume  
from TABLEA A 
     FULL OUTER JOIN TABLEB B 
         ON A.client_id=B.client_id 
        AND A.SUPPLIER_NO=B.SUPPLIER_NO
group by  A.client_id,  A.supplier_no
order by A.client_id,  A.supplier_no;

无论我是否使用client_idand进行连接，我都无法实现此输出supplier no。client_id在遍历后表 A 中不存在的情况下，我希望表 B 中的内容避免重复。表 A 只是查询的结果，表 B 是相同的，我想合并这些结果。

标签： sqloracle

解决方案

我认为这可以通过以下UNION ALL方式实现GROUP BY：

SELECT CLIENT_ID, SUPPLIER_NO,
MAX(INVOICE_COUNT) AS INVOICE_COUNT,
MAX(VOLUME) AS VOLUME,
MAX(REJECTED_COUNT) AS REJECTED_COUNT,
MAX(REJECTED_VOLUME) AS REJECTED_VOLUME
FROM
(SELECT CLIENT_ID, SUPPLIER_NO, INVOICE_COUNT, VOLUME, NULL AS REJECTED_COUNT, NULL REJECTED_VOLUME
FROM TABLEA
UNION ALL
SELECT CLIENT_ID, SUPPLIER_NO, NULL AS INVOICE_COUNT, NULL AS VOLUME, REJECTED_COUNT, REJECTED_VOLUME
FROM TABLEB)
GROUP BY CLIENT_ID, SUPPLIER_NO;

aggregate function根据您的要求使用。我用过max

干杯！！

推荐阅读