MySQL检测两个表中是否有相同数据

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句,可参考:
scott建表及录入数据sql脚本

问题:
要知道两个表或视图中是否有相同的数据(基数和值)。考虑这个视图

create view v
as
select * from emp where deptno != 10
union all
select * from emp where ename = 'WARD';

select * from v;
mysql> create view v
    -> as
    -> select * from emp where deptno != 10
    -> union all
    -> select * from emp where ename = 'WARD';
Query OK, 0 rows affected (0.03 sec)

mysql> select * from v;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7788 | SCOTT  | ANALYST  | 7566 | 1987-06-13 | 3000.00 |    NULL |     20 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK    | 7788 | 1987-06-13 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK    | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST  | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
12 rows in set (0.00 sec)

现在要检测这个视图与表EMP中的数据是否完全相同。
员工“WARD”行重复,说明解决方案不仅是要显示不同行,还要显示重复行。
因为在表EMP中部门10中的员工有3行,而对于员工“WARD”来说有2行。

解决方案:
使用关联子查询和union all来查找在视图V中存在而在表EMP中不存在的行。
然后在表EMP中存在而在视图V中不存在的行进行合并:

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(v.comm,0)
)
union all
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(v.comm,0)
)

执行记录:

mysql> 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(v.comm,0)
    -> )
    -> union all
    -> 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(v.comm,0)
    -> )
    -> ;
+-------+--------+-----------+------+------------+---------+--------+--------+-----+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm   | deptno | cnt |
+-------+--------+-----------+------+------------+---------+--------+--------+-----+
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 | 500.00 |     30 |   1 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |   NULL |     10 |   1 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |   NULL |     10 |   1 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |   NULL |     10 |   1 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 | 500.00 |     30 |   2 |
+-------+--------+-----------+------+------------+---------+--------+--------+-----+
5 rows in set (0.00 sec)

©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页