比较并查找 Oracle 中两个表的差异

Compare and find differences in two tables in Oracle

提问by Ruslan

I have 2 tables:

我有2张桌子：

1. account: ID, ACC, AE_CCY, DRCR_IND, AMOUNT, MODULE
2. flex: ID, ACC, AE_CCY, DRCR_IND, AMOUNT, MODULE

1. 帐户：ID, ACC, AE_CCY, DRCR_IND, AMOUNT, MODULE
2. 弹性：ID, ACC, AE_CCY, DRCR_IND, AMOUNT, MODULE

I want to show differences comparing only by: AE_CCY, DRCR_IND, AMOUNT, MODULEand ACCby first 4 characters

我只想通过以下方式显示差异：AE_CCY, DRCR_IND, AMOUNT, MODULE和ACC前 4 个字符

Example:

例子：

ID ACC       AE_CCY DRCR_IND AMOUNT MODULE
-- --------- ------ -------- ------ ------
1  734647674 USD    D        100    OP

and in flex:

并在弹性：

ID ACC       AE_CCY DRCR_IND AMOUNT MODULE
-- --------- ------ -------- ------ ------
1  734647654 USD    D        100    OP
2  734665474 USD    D        100    OP
9  734611111 USD    D        100    OP

ID's 2 and 9 should be shown as differences.

ID 的 2 和 9 应显示为差异。

If I use FULL JOINI'll get no differences as substr(account.ACC,1,4) = substr(flex.ACC,1,4)are equal and others are equal and MINUS doesn't work because ID's different.

如果我使用FULL JOIN我不会得到任何差异，因为substr(account.ACC,1,4) = substr(flex.ACC,1,4)是平等的，其他人是平等的，减号不起作用，因为 ID 不同。

回答by wbogacz

Do you mean you want to group by the first 4 characters of ACC, then diff them?

您的意思是要按 ACC 的前 4 个字符分组，然后对它们进行区分吗？

And, if not, why is Flex:ID=1 NOT a difference to account:ID=1, if ID=2 and ID=9 are, especially since it reads that ID is not a comparison field?

而且，如果不是，为什么 Flex:ID=1 与 account:ID=1 没有区别，如果 ID=2 和 ID=9 是，特别是因为它读取 ID 不是比较字段？

回答by Adam Musch

a brute-force set theory answer:

蛮力集理论答案：

SELECT * FROM ID 
UNION  
SELECT * FROM FLEX 
MINUS 
  (SELECT * FROM ID 
   INTERSECT  
   SELECT * FROM FLEX)

回答by Allan

I think what you want is the full join with an additional condition. Something like:

我认为您想要的是带有附加条件的完全连接。就像是：

select F.ID, F.AE_CCY, F.DRCR_IND, F.AMOUNT, F.MODULE, F.ACC 
from account a join flex f 
  on substr(a.ACC,1,4) = substr(f.ACC,1,4)
where a.AE_CCY <> f.AE_CCY 
   or a.DRCR_IND <> f.DRCR_IND 
   or a.AMOUNT <> f.AMOUNT
   or a.MODULE <> f.MODULE
   or a.ACC <> f.ACC

This way, the join is still performed on the first 4 characters, but the where condition checks the entire field (as well as the other four).

这样，连接仍然在前 4 个字符上执行，但 where 条件检查整个字段（以及其他四个）。

Revised solution: This is something of a stab-in-the-dark, by I'm wondering if what you're really looking for is a list of records that don't have a match in the other table. In that case, a full outer join might be the answer:

修订后的解决方案：这有点像暗中刺伤，我想知道您真正要寻找的是其他表中没有匹配项的记录列表。在这种情况下，完整的外连接可能是答案：

select coalesce(F.ID,a.ID) as ID, 
       coalesce(F.AE_CCY,a.AE_CCY) as AE_CCY, 
       coalesce(F.DRCR_IND,a.DRCR_IND) as DRCR_IND, 
       coalesce(F.AMOUNT,a.AMOUNT) as AMOUNT, 
       coalesce(F.MODULE,a.MODULE) as MODULE, 
       coalesce(F.ACC,a.ACC) as ACC
from account a full outer join flex f 
  on substr(a.ACC,1,4) = substr(f.ACC,1,4)
     and a.AE_CCY = f.AE_CCY 
     and a.DRCR_IND = f.DRCR_IND 
     and a.AMOUNT = f.AMOUNT
     and a.MODULE = f.MODULE
where a.id is null
   or f.id is null

Third attempted solution: Thinking about it further, I think you're saying that you want each record from the first table to match to exactly one record in the second table (and vice-versa). That's a difficult problem because relational databases aren't really design work that way.

第三个尝试的解决方案：进一步考虑一下，我认为您是说您希望第一个表中的每条记录与第二个表中的一条记录匹配（反之亦然）。这是一个难题，因为关系数据库并不是真正的设计工作那样。

The solution below uses the full outer join again, to get only rows that don't appear in the other table. This time, we're adding ROW_NUMBER to assign a unique number to each member of a set of duplicate values found in either table. In the example from your comment, with 5 identical rows in one table and 1 of the same row in another, the first table will be numbered 1-5 and the second will be 1. Therefore, by adding that as a join condition, we assure that each row has only one match. The one flaw in this design is that a perfect match on ACC is not guaranteed to take precedence over another value. Making that work would be quite a bit more difficult.

下面的解决方案再次使用完整的外部联接，以仅获取未出现在其他表中的行。这一次，我们将添加 ROW_NUMBER 以将唯一编号分配给在任一表中找到的一组重复值的每个成员。在您评论的示例中，一个表中有 5 个相同的行，另一个表中有 1 个相同的行，第一个表的编号为 1-5，第二个表的编号为 1。因此，通过将其添加为连接条件，我们确保每一行只有一个匹配项。这种设计的一个缺陷是不能保证 ACC 的完美匹配优先于另一个值。使这项工作更加困难。

select coalesce(F.ID,a.ID) as ID, 
       coalesce(F.AE_CCY,a.AE_CCY) as AE_CCY, 
       coalesce(F.DRCR_IND,a.DRCR_IND) as DRCR_IND, 
       coalesce(F.AMOUNT,a.AMOUNT) as AMOUNT, 
       coalesce(F.MODULE,a.MODULE) as MODULE, 
       coalesce(F.ACC,a.ACC) as ACC
from (select a.*, 
             row_number() 
             over (partition by AE_CCY,DRCR_IND,AMOUNT,MODULE,substr(ACC,1,4) 
                   order by acc) as rn 
      from account a) a 
     full outer join
     (select f.*, 
             row_number() 
             over (partition by AE_CCY,DRCR_IND,AMOUNT,MODULE,substr(ACC,1,4) 
                   order by acc) as rn 
      from flex f) f
     on substr(a.ACC,1,4) = substr(f.ACC,1,4)
     and a.AE_CCY = f.AE_CCY 
     and a.DRCR_IND = f.DRCR_IND 
     and a.AMOUNT = f.AMOUNT
     and a.MODULE = f.MODULE
     and a.RN = f.RN
where a.id is null
   or f.id is null

回答by Vincent Malgrat

I like to use:

我喜欢使用：

SELECT min(which) which, id, ae_ccy, drcr_ind, amount, module, acc
  FROM (SELECT DISTINCT 'account' which, id, ae_ccy, drcr_ind, amount, module, 
               substr(acc, 1, 4) acc
          FROM ACCOUNT
        UNION ALL
        SELECT DISTINCT 'flex' which, id, ae_ccy, drcr_ind, amount, module, 
               substr(acc, 1, 4) acc
          FROM flex)
 GROUP BY id, ae_ccy, drcr_ind, amount, module, acc
HAVING COUNT(*) != 2
 ORDER BY id, 1

It will show both the new rows, the old missing rows and any difference.

它将显示新行、旧的缺失行和任何差异。