MySQLで同一カラム値が複数レコードに重複している行を抽出する方法

MySQLでは、JOIN（自己結合）を使用することで、同じカラムに同一のデータが複数のレコードにまたがって存在する行を抽出できます。まずはサンプルとなるテーブルを作成しましょう。

mysql> create table DemoTable
(
    UserId int,
    UserName varchar(20)
);
Query OK, 0 rows affected (0.54 sec)

次に、INSERTコマンドを使ってテーブルにレコードを挿入します。

mysql> insert into DemoTable values(10,'John');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values(11,'Sam');
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable values(12,'Larry');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values(13,'David');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values(14,'Larry');
Query OK, 1 row affected (0.10 sec)

SELECTコマンドでテーブルの内容を確認しましょう。

mysql> select *from DemoTable;

上記のコマンドを実行すると、次のような出力が得られます。

+--------+----------+
| UserId | UserName |
+--------+----------+
| 10     | John     |
| 11     | Sam      |
| 12     | Larry    |
| 13     | David    |
| 14     | Larry    |
+--------+----------+
5 rows in set (0.00 sec)

ここで「UserName」カラムを見ると、「Larry」という名前が2つのレコード（UserId：12 と 14）に重複して登録されていることがわかります。

重複データを含む行を抽出するクエリ

以下が、同一カラムに同じデータが複数のレコードに含まれている行を抽出するためのクエリです。

mysql> SELECT DISTINCT tbl1.*
FROM DemoTable tbl1
JOIN DemoTable tbl2
on tbl2.UserId <> tbl1.UserId
AND tbl2.UserName=tbl1.UserName;

このクエリを実行すると、次のような結果が出力されます。

+--------+----------+
| UserId | UserName |
+--------+----------+
| 14     | Larry    |
| 12     | Larry    |
+--------+----------+
2 rows in set (0.14 sec)

クエリの仕組み

このクエリのポイントは以下の通りです。

• 自己結合： DemoTableに対して自分自身とJOINを行うことで、異なるレコード同士を比較できるようにしています。
• UserId <> UserId の条件： 同じレコード同士ではなく、「UserId」が異なるレコードの組み合わせだけを対象にします。
• UserName = UserName の条件： 名前が一致する組み合わせのみを結合するため、同じ名前を持つ別々のレコードだけが抽出されます。
• DISTINCT： 重複した結果行を排除し、各レコードを一度だけ表示します。

なお、GROUP BYとHAVING句を組み合わせる方法でも同様の結果が得られます。データ量や目的に応じて最適な手法を選択するとよいでしょう。