MySQLは、2つのテーブルに同じデータがあるかどうかを検出します.

43555 ワード

同前 MySQL CookBook MySQLは小さい時から専門家への道です。 mysql データベース

備考:テストデータベースバージョンはMySQL 8.0
scottユーザーがテーブルを作成し、データ文を入力する必要がある場合は、scottテーブルおよびデータsqlスクリプトを参照してください.
質問:2つのテーブルまたはビューに同じデータ(ベースと値)があるかどうかを確認します.このビューを考慮

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)

位置:粘着性

オブジェクトの配列から重複するオブジェクトを削除する