MySQLでデータベースから重複レコードのみを抽出し、件数も表示する方法

MySQLで重複レコードのみを抽出し、件数を表示する方法

データベースから重複しているレコードだけを取り出し、その出現回数を一緒に表示したいケースはよくあります。そんなときは、集計関数 COUNT() と HAVING 句を組み合わせるのが基本です。WHERE 句は集計前の個々の行に対する条件しか指定できませんが、HAVING 句なら GROUP BY でグループ化した後の結果に条件を適用できるため、「重複しているグループだけ」を簡単に絞り込めます。

手順1：サンプルテーブルを作成する

まずは動作確認用のテーブルを作成しましょう。

mysql> create table duplicateRecords
    -> (
    -> ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> ClientName varchar(20)
    -> );
Query OK, 0 rows affected (0.49 sec)

手順2：テストデータを挿入する

続いて、INSERT コマンドでレコードを登録します。ここでは意図的に同じ名前（John、Sam）を複数回挿入しています。

mysql> insert into duplicateRecords(ClientName) values('John');
Query OK, 1 row affected (0.16 sec)
mysql> insert into duplicateRecords(ClientName) values('Carol');
Query OK, 1 row affected (0.17 sec)
mysql> insert into duplicateRecords(ClientName) values('John');
Query OK, 1 row affected (0.29 sec)
mysql> insert into duplicateRecords(ClientName) values('Sam');
Query OK, 1 row affected (0.19 sec)
mysql> insert into duplicateRecords(ClientName) values('Sam');
Query OK, 1 row affected (0.11 sec)
mysql> insert into duplicateRecords(ClientName) values('Bob');
Query OK, 1 row affected (0.12 sec)
mysql> insert into duplicateRecords(ClientName) values('John');
Query OK, 1 row affected (0.13 sec)
mysql> insert into duplicateRecords(ClientName) values('Sam');
Query OK, 1 row affected (0.12 sec)

手順3：全レコードを確認する

SELECT 文でテーブルの中身を確認してみましょう。

mysql> select * from duplicateRecords;

実行すると、合計8件のレコードが表示されます。

+----------+------------+
| ClientId | ClientName |
+----------+------------+
| 1        | John       |
| 2        | Carol      |
| 3        | John       |
| 4        | Sam        |
| 5        | Sam        |
| 6        | Bob        |
| 7        | John       |
| 8        | Sam        |
+----------+------------+
8 rows in set (0.00 sec)

手順4：重複レコードのみを抽出するクエリ

ここが本題です。ClientName ごとにグループ化し、COUNT(*) の結果が 1 より大きいグループ（＝重複している名前）だけを HAVING 句で抽出します。

mysql> select ClientName,count(*) as DuplicateRecord
    -> from duplicateRecords
    -> group by ClientName
    -> having DuplicateRecord > 1;

実行結果は以下の通りです。John と Sam がそれぞれ 3 回ずつ登場していることがわかります。

+------------+-----------------+
| ClientName | DuplicateRecord |
+------------+-----------------+
| John       | 3               |
| Sam        | 3               |
+------------+-----------------+
2 rows in set (0.00 sec)

クエリのポイント

• group by ClientName： 同じ名前のレコードをひとつのグループにまとめます。
• count(*) as DuplicateRecord： 各グループの行数を数え、別名「DuplicateRecord」として表示します。
• having DuplicateRecord > 1： 件数が 2 以上のグループ、つまり重複しているレコードだけを残します。エイリアスを HAVING 内で参照できるのは MySQL 特有の拡張機能です。標準SQLに準拠した書き方にしたい場合は having count(*) > 1 と記述するのが安全です。

なお、単一カラムではなく複数カラムの組み合わせで重複を判定したい場合は、GROUP BY にそのカラムをすべて指定すれば、同じ要領で抽出できます。また、重複データの削除やデータクレンジングを行う前の調査段階でも、このクエリは非常に役立ちます。