SQLで重複レコードを操作

2017年10月19日2022年6月4日

データベースで重複レコードを抽出する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

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

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

↓が基本形です。

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

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');

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