检测同一表的两个版本之间的差异 [英] Detect differences between two versions of the same table

问题描述

我正在寻找一种方法来检测同一张表的两个版本之间的差异.假设我在两个不同的日期创建了一个活动表的副本:

I am looking for a method to detect differences between two versions of the same table. Let's say I create copies of a live table at two different days:

第1天:

CREATE TABLE table_1 AS SELECT * FROM table

第2天:

CREATE TABLE table_2 AS SELECT * FROM table 

该方法应标识在第一天到第二天之间添加，删除或更新的所有行；如果可能，该方法不应使用RDBMS特定的功能；

The method should identify all rows added, deleted or updated between day 1 and day 2; if possible the method should not use a RDBMS-specific feature;

注意:将表的内容导出到文本文件并比较文本文件是可以的，但是我想要一个SQL特定的方法.

Note: Exporting the content of the table to text files and comparing text files is fine, but I would like a SQL specific method.

示例:

create table table_1 
(
 col1 integer,
 col2 char(10)
);
create table table_2
(
 col1 integer,
 col2 char(10)
);    
insert into table_1 values ( 1, 'One' );
insert into table_1 values ( 2, 'Two' );
insert into table_1 values ( 3, 'Three' );
insert into table_2 values ( 1, 'One' );
insert into table_2 values ( 2, 'TWO' );
insert into table_2 values ( 4, 'Four' );

table_1与table_2之间的差异:

Differences between table_1 and table_2:

• 已添加:行(4，'四个')
• 已删除:行(3，'Three')
• 已更新:行(2，'Two')已更新为(2，'TWO')

推荐答案

我想我找到了答案-一个人可以使用此SQL语句构建差异列表:

I think I found the answer - one can use this SQL statement to build a list of differences:

注意:"col1，col2"列表必须包括表中的所有列

Note: "col1, col2" list must include all columns in the table

SELECT
MIN(table_name) as table_name, col1, col2
FROM
(
   SELECT
   'Table_1' as table_name, col1, col2
   FROM Table_1 A
   UNION ALL
   SELECT
   'Table_2' as table_name, col1, col2
   FROM Table_2 B
)
tmp
GROUP BY col1, col2
HAVING COUNT(*) = 1

+------------+------+------------+
| table_name | col1 |    col2    |
+------------+------+------------+
| Table_2    |    2 | TWO        |
| Table_1    |    2 | Two        |
| Table_1    |    3 | Three      |
| Table_2    |    4 | Four       |
+------------+------+------------+

在问题中引用的示例中，

In the example quoted in the question,

• 表_2中存在行(4，四个")；确定"已添加"行
• 表_1中存在行(3，三个")；判断行"已删除"
• 仅在表_1中存在行(2，'Two')；行(2，'TWO')仅存在于table_2中；如果col1是主键，则判断为"已更新"

• Row ( 4, 'Four' ) present in table_2 ; verdict row "Added"
• Row ( 3, 'Three' ) present in table_1; verdict row "Deleted"
• Row ( 2, 'Two' ) present in table_1 only; Row ( 2, 'TWO' ) present in table_2 only; if col1 is primary key then verdict "Updated"

这篇关于检测同一表的两个版本之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！