检测同一表的两个版本之间的差异 [英] 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屋!