Database MariaDB/MySQL OracleDB PostgreSQL インフラ

SQLで重複レコードを操作

2017年10月19日

データベースで重複レコードを抽出するSQLを紹介します。

グーグル先生に問いかけても良い感じの答えが返ってこないので公開します。

※このSQLはOracleDBを元に作成しております。


今回使用するデータ

今回は↓のデータを前提に説明します。

 > select col01, col02, col03 from test001.tab001
2   order by col01;

COL01 COL02           COL03
----- --------------- ------
0001  TOKYO           AAAAA
0001  TOKYO           BBBBB
0002  OKINAWA         CCCCC
0003  HOKKAIDO        DDDDD
0004  TOKYO           EEEEE
0005  OKINAWA         FFFFF
0006  KANAGAWA        GGGGG
0007  FUKUOKA         HHHHH

0001:TOKYOの2つと0004:TOKYO

0002:OKINAWAと0005:OKINAWA

が重複しています。

※COL03は最後に応用編で使います。

重複レコード行を除外

最も一般的な重複レコードの操作です。

もちろんDISTINCTを使用します。

select distinct 列名 from 表名;

COL02列の重複を除外した場合は↓が実行結果です。

> select distinct col02 from test001.tab001;

COL02
----------
KANAGAWA
FUKUOKA
OKINAWA
TOKYO
HOKKAIDO

もちろんDISTINCT句は条件を複数にすることもできます。

select distinct 列名 , 列名... from 表名;

その場合をDISTINCTに指定した列を論理積として考えて重複を除外します。

↓が実行ログです。

 > select distinct col01 , col02 from test001.tab001
2  order by col01;

COL01 COL02
----- ----------
0001  TOKYO
0002  OKINAWA
0003  HOKKAIDO
0004  TOKYO
0005  OKINAWA
0006  KANAGAWA
0007  FUKUOKA

 

重複しているレコードを抽出

恐らくこれを皆さん知りたいんでしょw

↓が基本形です。

select 表名
  from 表名
  group by 重複対象列 having count(重複対象列) <> 1;

COL02で重複指定列を取得したい場合は↓です。

 > select col02
2  from test001.tab001
3  group by col02 having count(col02) <> 1;

COL02
----------
OKINAWA
TOKYO

COL01とCOL2の論理積で取得したい場合は↓です。

 > select col01,col02
2  from test001.tab001
3  group by col01, col02 having count(col01) <> 1;

COL01 COL02
----- ----------
0001  TOKYO

 > select col01,col02
2  from test001.tab001
3  group by col01, col02 having count(col02) <> 1;

COL01 COL02
----- ----------
0001  TOKYO

 > select col01,col02
2  from test001.tab001
3  group by col01, col02 having count(*) <> 1;

COL01 COL02
----- ----------
0001  TOKYO

↑のようにHAVING COUNT~は3つのどのパターンでもいいゾ

※厳密にはちょっと違うけど結果は同じだからOK!

また「HAVING COUNT > 1」でも結果は同じです。

応用:重複しているレコードの”全列を抽出”

全列を抽出する場合には↓のように副問い合わせを使用す必要があります。

select * 
  from 表名
 where (列名)
        in ( select 列名
               from 表名
              group by 列名 having count(列名) <> 1 );

↓がCOL02で重複した行の取得ログです。

 > select *
2    from test001.tab001
3   where (col02)
4          in ( select col02
5                 from test001.tab001
6                group by col02 having count(col02) <> 1 );

COL01 COL02           COL03
----- --------------- ------
0001  TOKYO           AAAAA
0001  TOKYO           BBBBB
0002  OKINAWA         CCCCC
0004  TOKYO           EEEEE
0005  OKINAWA         FFFFF

複数列の重複を確認したい場合は↓です。

select * 
  from 表名
 where (列名, 列名 ...)
        in ( select 列名, 列名 ...
               from 表名
              group by 列名, 列名 ... having count(列名) <> 1 );

↓がCOL01のCOL02の論理積で重複した行の取得ログです。

 > select *
2    from test001.tab001
3   where (col01, col02)
4          in ( select col01, col02
5                 from test001.tab001
6                group by col01, col02 having count(1) <> 1 );

COL01 COL02           COL03
----- --------------- ------
0001  TOKYO           AAAAA
0001  TOKYO           BBBBB

上級編:EXISTSを使用して重複レコードの”全列を抽出”

EXISTSを使用することで余計でデータ読み込みを行わず高速に処理をすることができます。

個人的にはですが、SQLの可読性は大きく落ちます。

↓のように記載します。

 > select col01, col02, col03
2    from test001.tab001 a
3   where exists
4      (select 1 from test001.tab001
5        where a.col01 = col01
6          and a.col02 = col02
7        group by col01, col02 having count(1) <> 1)
8   order by col01;

COL01 COL02           COL03
----- --------------- ------
0001  TOKYO           BBBBB
0001  TOKYO           AAAAA

 

おまけ:テストデータの作成

テストデータの作成は↓です。

create table test001.tab001(
  col01 varchar2(20),
  col02 varchar2(20),
  col03 varchar2(20)
)
;

insert into test001.tab001 values('0001','TOKYO','AAAAA');
insert into test001.tab001 values('0001','TOKYO','BBBBB');
insert into test001.tab001 values('0002','OKINAWA','CCCCC');
insert into test001.tab001 values('0003','HOKKAIDO','DDDDD');
insert into test001.tab001 values('0004','TOKYO','EEEEE');
insert into test001.tab001 values('0005','OKINAWA','FFFFF');
insert into test001.tab001 values('0006','KANAGAWA','GGGGG');
insert into test001.tab001 values('0007','FUKUOKA','HHHHH');

みんなもデータベースに入れて試してみよう!!

関連記事

-Database, MariaDB/MySQL, OracleDB, PostgreSQL, インフラ

Copyright© shimidai2100 , 2020 All Rights Reserved.