描述:
想知道两个表或两个视图里是否有相同的数据(行数和值)。考虑如下所示的视图。
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 和 PostgreSQLMINUS
和 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))
来源地址:https://blog.csdn.net/m0_67621290/article/details/128934531