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) |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |