SQL 确定两个表是否有相同的数据

sql 数据库 mysql 2023-09-05 07:09:33 111人浏览安东尼

摘要

描述：想知道两个表或两个视图里是否有相同的数据（行数和值）。考虑如下所示的视图。 create view Vasselect * from emp where deptno != 10 uNIOn allse

描述：

想知道两个表或两个视图里是否有相同的数据（行数和值）。考虑如下所示的视图。

create view Vasselect * from emp where deptno != 10 uNIOn allselect * from emp where ename = 'WARD'　select * from V

我希望确定该视图是否和 EMP 表有完全相同的数据。与员工 WARD 相关的数据有两行，这表明相应的解决方案不仅要找出来不同的数据，还要找到重复的数据。根据 EMP 表的数据，二者的不同之处包括 3 行部门编号为 10 的数据以及两行员工 WARD 的数据。我希望返回如下所示的结果集。

方法：

使用求差集的函数（MINUS 或 EXCEPT，这取决于你使用的数据库管理系统）可以很容易地比较表中的数据。如果你所使用的数据库管理系统没有提供类似功能，则可以使用关联子查询。

DB2 和 PostgreSQL
使用集合运算 EXCEPT 和 UNioN ALL 找出视图 V 和 EMP 表的不同之处。

 1  ( 2   select empno,ename,job,mgr,hiredate,sal,comm,deptno, 3           count(*) as cnt 4     from V 5    group by empno,ename,job,mgr,hiredate,sal,comm,deptno 6   except 7   select empno,ename,job,mgr,hiredate,sal,comm,deptno, 8          count(*) as cnt 9     from emp10    group by empno,ename,job,mgr,hiredate,sal,comm,deptno11  )12    union all13  (14   select empno,ename,job,mgr,hiredate,sal,comm,deptno,15          count(*) as cnt16     from emp17    group by empno,ename,job,mgr,hiredate,sal,comm,deptno18   except19   select empno,ename,job,mgr,hiredate,sal,comm,deptno,20          count(*) as cnt21     from V22    group by empno,ename,job,mgr,hiredate,sal,comm,deptno23  )

Oracle
使用集合运算 MINUS 和 UNION ALL 找出视图 V 和 EMP 表的不同之处。

 1  ( 2   select empno,ename,job,mgr,hiredate,sal,comm,deptno, 3          count(*) as cnt 4     from V 5    group by empno,ename,job,mgr,hiredate,sal,comm,deptno 6    minus 7   select empno,ename,job,mgr,hiredate,sal,comm,deptno, 8          count(*) as cnt 9     from emp10    group by empno,ename,job,mgr,hiredate,sal,comm,deptno11  )12    union all13  (14   select empno,ename,job,mgr,hiredate,sal,comm,deptno,15          count(*) as cnt16     from emp17    group by empno,ename,job,mgr,hiredate,sal,comm,deptno18    minus19   select empno,ename,job,mgr,hiredate,sal,comm,deptno,20          count(*) as cnt21     from v22    group by empno,ename,job,mgr,hiredate,sal,comm,deptno23  )

MySQL 和 SQL Server
使用关联子查询和 UNION ALL 找出那些存在于视图 V 而不存在于 EMP 表的数据，以及存在于 EMP 表而不存在于视图 V 的数据，并将它们合并起来。

 1   select * 2     from ( 3   select e.empno,e.ename,e.job,e.mgr,e.hiredate, 4          e.sal,e.comm,e.deptno, count(*) as cnt 5     from emp e 6    group by empno,ename,job,mgr,hiredate, 7             sal,comm,deptno 8          )e 9    where not exists (10   select null11     from (12   select v.empno,v.ename,v.job,v.mgr,v.hiredate,13          v.sal,v.comm,v.deptno, count(*) as cnt14     from v15    group by empno,ename,job,mgr,hiredate,16             sal,comm,deptno17          )v18    where v.empno    = e.empno19      and v.ename    = e.ename20      and v.job      = e.job21      and v.mgr      = e.mgr22      and v.hiredate = e.hiredate23      and v.sal      = e.sal24      and v.deptno   = e.deptno25      and v.cnt      = e.cnt26      and coalesce(v.comm,0) = coalesce(e.comm,0)27   )28    union all29   select *30     from (31   select v.empno,v.ename,v.job,v.mgr,v.hiredate,32          v.sal,v.comm,v.deptno, count(*) as cnt33     from v34    group by empno,ename,job,mgr,hiredate,35             sal,comm,deptno36          )v37    where not exists (38   select null39     from (40   select e.empno,e.ename,e.job,e.mgr,e.hiredate,41          e.sal,e.comm,e.deptno, count(*) as cnt42     from emp e43    group by empno,ename,job,mgr,hiredate,44             sal,comm,deptno45          )e46    where v.empno    = e.empno47      and v.ename    = e.ename48      and v.job      = e.job49      and v.mgr      = e.mgr50      and v.hiredate = e.hiredate51      and v.sal      = e.sal52      and v.deptno   = e.deptno53      and v.cnt      = e.cnt54      and coalesce(v.comm,0) = coalesce(e.comm,0)55   )

扩展知识：

尽管使用了不同的方法，但上述解决方案的原理并无差别。
(1) 首先，找出存在于 EMP 表而不存在于视图 V 的行；
(2) 然后与存在于视图 V 而不存在于 EMP 表的行合并（UNION ALL）。
如果两个表完全相同，则不会返回任何数据。如果两个表有不同之处，那么将返回那些不同的行。在比较两个表的时候，比较容易的做法是，在比较数据之前先单独比较行数。下面是一个行数比较的简单示例，适用于所有数据库管理系统。

select count(*)  from emp unionselect count(*)  from dept

因为 UNION 子句会过滤掉重复项，所以如果两个表的行数相同，则只会返回一行数据。本例中返回了两行数据，这说明两个表中没有完全相同的数据。
DB2、Oracle 和 PostgreSQL
MINUS 和 EXCEPT 的作用相同，所以这里只讨论 EXCEPT。UNION ALL 前后的两个查询语句非常相似。因此，为了说明这个解决方案的原理，我们将直接执行位于 UNION ALL 前面的那个查询。执行第 1 行至第 11 行后产生的结果集如下所示。

(  select empno,ename,job,mgr,hiredate,sal,comm,deptno,         count(*) as cnt    from V   group by empno,ename,job,mgr,hiredate,sal,comm,deptno  except  select empno,ename,job,mgr,hiredate,sal,comm,deptno,         count(*) as cnt    from emp   group by empno,ename,job,mgr,hiredate,sal,comm,deptno)

上述结果集显示从视图 V 中查询到了一行数据，该行数据要么不存在于 EMP 表，要么它在视图 V 中出现的次数与 EMP 表中的不一致。对于本例而言，查询找到了员工 WARD 的重复行。如果你仍然不理解该结果集是如何产生的，可以分别执行位于 EXCEPT 前后的两个查询。你会发现，两个结果集的不同之处仅仅在于视图 V 中员工 WARD 相关行的 CNT 值。
位于 UNION ALL 后面的查询语句执行了和 UNION ALL 前面的查询相反的操作。该查询找出了那些存在于 EMP 表而不存在于视图 V 的行。

(  select empno,ename,job,mgr,hiredate,sal,comm,deptno,         count(*) as cnt    from emp   group by empno,ename,job,mgr,hiredate,sal,comm,deptno   minus  select empno,ename,job,mgr,hiredate,sal,comm,deptno,         count(*) as cnt    from v   group by empno,ename,job,mgr,hiredate,sal,comm,deptno)

上述两个结果集通过 UNION ALL 合并后即可得到最终的结果集。

MySQL 和 SQL Server
位于 UNION ALL 前后的两个查询语句非常相似。为了理解基于子查询的解决方案，我们直接执行 UNION ALL 前面的查询。下面的查询是第 1 行至第 27 行的内容。

select *   from ( select e.empno,e.ename,e.job,e.mgr,e.hiredate,        e.sal,e.comm,e.deptno, count(*) as cnt   from emp e  group by empno,ename,job,mgr,hiredate,           sal,comm,deptno        ) e  where not exists (select null  from (select v.empno,v.ename,v.job,v.mgr,v.hiredate,       v.sal,v.comm,v.deptno, count(*) as cnt  from v group by empno,ename,job,mgr,hiredate,          sal,comm,deptno       ) v  where v.empno    = e.empno    and v.ename    = e.ename    and v.job      = e.job    and v.mgr      = e.mgr    and v.hiredate = e.hiredate    and v.sal      = e.sal    and v.deptno   = e.deptno    and v.cnt      = e.cnt    and coalesce(v.comm,0) = coalesce(e.comm,0))

注意，这里比较的不是 EMP 表和视图 V，而是内嵌视图 E 和内嵌视图 V。计算出每一行数据出现的次数，并作为查询结果的一列返回。我们要比较每一行的数据及其出现的次数。如果你还是不理解比较操作是如何执行的，不妨单独执行两个子查询。下一步是找出存在于内嵌视图 E 而不存在于内嵌视图 V 的所有行（包括 CNT）。该操作使用了关联子查询和 NOT EXISTS。连接查询将确定哪些行是相同的，NOT EXISTS 则筛选出内嵌视图 E 中与连接查询结果不匹配的行。UNION ALL 后面的查询语句做了相反的操作，它找出了所有存在于内嵌视图 V 而不存在于内嵌视图 E 的行。

select *   from (select v.empno,v.ename,v.job,v.mgr,v.hiredate,       v.sal,v.comm,v.deptno, count(*) as cnt  from v group by empno,ename,job,mgr,hiredate,          sal,comm,deptno       ) v  where not exists (select null  from (select e.empno,e.ename,e.job,e.mgr,e.hiredate,       e.sal,e.comm,e.deptno, count(*) as cnt  from emp e group by empno,ename,job,mgr,hiredate,          sal,comm,deptno      ) e where v.empno    = e.empno   and v.ename    = e.ename   and v.job      = e.job   and v.mgr      = e.mgr   and v.hiredate = e.hiredate   and v.sal      = e.sal   and v.deptno   = e.deptno   and v.cnt      = e.cnt   and coalesce(v.comm,0) = coalesce(e.comm,0))