sql - 两个表之间的差异(使用 sql)-> 增量更改

Question

我需要确定两个表之间的差异。我看过sql query to return differences between two tables但对我来说，用我目前的 SQL 技能进行推断有点太不同了。

表 A 是昨天拍摄的特定人群的快照，其中每一行是一个独特的人以及关于该人的某些特征。表 B 是 24 小时后拍摄的相同快照。 24小时内:

可能已经添加了新人。

昨天的人可能已被删除。

昨天的人可能已经改变了(即，原来的行还在，但一个或多个列的值已经改变了)。

我的输出应该有以下内容:

每添加一个新人一行

删除的每个人一行

每个改变的人一行

我将不胜感激任何想法。谢谢!

Answer 1

这类问题有一个非常简单有效的解决方案，不使用连接 (它甚至不使用两个 MINUS 运算结果的联合)——它只使用一个联合和一个 GROUP BY 运算。该解决方案是多年前在 AskTom 上的一个线程中开发的，令人惊讶的是它并没有被更广泛地了解和使用。例如(但不仅限于):https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:24371552251735

在您的情况下，假设 PERSON_ID 上有主键约束(这使解决方案更简单):

select max(flag) as flag, PERSON_ID, first_name, last_name, (etc. - all the columns)
from   ( select 'old' as flag, t1.*
           from old_table t1
         union all
         select 'new' as flag, t2.*
           from new_table t2
       )
group by PERSON_ID, first_name, last_name, (etc.)
having  count(*) = 1
order by PERSON_ID  --     optional
;

如果为 PERSON_ID两个表中的所有数据都相同，这将导致该组的计数为 2。所以它不会通过 HAVING 条件。唯一计数为 1 的组(因此每个组只有一行!)要么是一个表中的行，要么是另一个表中的行。如果添加了一个人，那将只显示一行，带有标志 = 'new'。如果一个人被删除，您将只得到一行，标记为“旧”。如果有更新，同样 PERSON_ID将出现两次，但由于至少有一个字段不同，因此两行(一行带有“new”标志，另一行带有“old”标志)将在不同的组中，它们将通过 HAVING 过滤器，并且它们都将在输出。

这与您要求的略有不同；您将获得更新的旧信息和新信息，标记为“旧”和"new"。你说你只想要其中之一，但没有说明是哪一个。这将为您提供两者(无论如何这更有意义)，但如果您真的只想要一个，则可以在上面的查询中轻松完成。

注-外 select必须有 max(flag)而不是 flag因为 flag不是 GROUP BY柱子;但它是 max()正好在一行上，所以它将是 flag无论如何，对于那一行。

已添加 - OP 表示他只想为具有更新(更改、修改)数据的人获取"new"行。在这种情况下，下面显示的方法会将标志更改为“已更改”。

with old_table ( person_id, first_name, last_name ) as (
       select 101, 'John', 'Smith' from dual union all
       select 102, 'Mary', 'Green' from dual union all
       select 103, 'July', 'Dobbs' from dual union all
       select 104, 'Will', 'Scott' from dual
     ),
     new_table ( person_id, first_name, last_name ) as (
       select 101, 'Joe' , 'Smith' from dual union all
       select 102, 'Mary', 'Green' from dual union all
       select 104, 'Will', 'Scott' from dual union all
       select 105, 'Andy', 'Brown' from dual
     )
-- end of test data; solution (SQL query) begins below this line
select case ct when 1 then flag else 'changed' end as flag,
       person_id, first_name, last_name
from   (
select max(flag) as flag, person_id, first_name, last_name,
       count(*) over (partition by person_id) as ct,
       row_number() over (partition by person_id order by max(flag)) as rn
from   ( select 'old' as flag, t1.*
           from old_table t1
         union all
         select 'new' as flag, t2.*
           from new_table t2
       )
group by person_id, first_name, last_name
having  count(*) = 1
)
where rn = 1
order by person_id   --  ORDER BY clause is optional
;

输出 :

FLAG     PERSON_ID FIRS_NAME LAST_NAME
------- ---------- --------- ---------
changed        101 Joe       Smith
old            103 July      Dobbs
new            105 Andy      Brown