在Oracle中使用SQL检查表是否相同 [英] Check if tables are identical using SQL in Oracle

问题描述

在一个初级Oracle开发人员职位的面试中有人问我这个问题，面试官承认这是一个艰难的工作：

I was asked this question during an interview for a Junior Oracle Developer position, the interviewer admitted it was a tough one:

写一个查询来检查是否表'employees_hist'是表'employees'的精确副本。任何想法如何解决？

Write a query/queries to check if the table 'employees_hist' is an exact copy of the table 'employees'. Any ideas how to go about this?

编辑：考虑到表可以有重复的记录，因此在这种情况下，简单的MINUS将不起作用。

Consider that tables can have duplicate records so a simple MINUS will not work in this case.

示例

EMPLOYEES

NAME
--------
Jack Crack
Jack Crack
Jill Hill

这两个不完全相同。

EMPLOYEES_HIST

NAME
--------
Jack Crack
Jill Hill
Jill Hill

推荐答案

一种解决重复的方法是创建一个子查询，该子查询对两个表进行UNION，并通过对所有表进行分组来包括每个表中包含的重复数。列。然后，外部查询可以对所有列进行分组，包括行计数列。如果表匹配，则不应返回任何行：

One possible solution, which caters for duplicates, is to create a subquery which does a UNION on the two tables, and includes the number of duplicates contained within each table by grouping on all the columns. The outer query can then group on all the columns, including the row count column. If the table match, there should be no rows returned:

create table employees (name varchar2(100));
create table employees_hist (name varchar2(100));

insert into employees values ('Jack Crack');
insert into employees values ('Jack Crack');
insert into employees values ('Jill Hill');
insert into employees_hist values ('Jack Crack');
insert into employees_hist values ('Jill Hill');
insert into employees_hist values ('Jill Hill');


with both_tables as
(select name, count(*) as row_count
 from employees
 group by name
union all
 select name, count(*) as row_count
 from employees_hist
 group by name)
select name, row_count from both_tables
group by name, row_count having count(*) <> 2;

为您提供：

Name        Row_count
Jack Crack  1
Jack Crack  2
Jill Hill   1
Jill Hill   2

这告诉您两个名称在一个表中出现一次，在另一个表中出现两次，因此表不匹配。

This tells you that both names appear once in one table and twice in the other, and therefore the tables don't match.

这篇关于在Oracle中使用SQL检查表是否相同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！