mysql两条完全相同的数据,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)