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

72 阅读 0 评论 48 点赞

我是靠谱客的博主不安信封，最近开发中收集的这篇文章主要介绍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)