【MySQL】重複しているデータのみを抽出したい（DISTINCTの逆）

f:id:y_hakoiri:20191102121915j:plain

MySQLで重複データを除外してSELECTしたいときにDISTINCTを使うけど、反対に重複のあるレコードだけを抽出したり、重複のあるレコードの中身を知りたいな〜って時にどうすれ良いのかなって疑問に思いメモ。

テストデータ

Server version: 5.7.34

こんなデータを用意しました。

mysql> select id, login_id from users;
+----+----------+
| id | login_id |
+----+----------+
|  1 | hoge     |
|  2 | fuga     |
|  3 | piyo     |
|  4 | hogehoge |
|  5 | fugafuga |
|  6 | piyopiyo |
|  7 | hoge     |
|  8 | fuga     |
+----+----------+
8 rows in set (0.01 sec)

DISTINCTするとこうなる。

mysql> select distinct(login_id) from users;
+----------+
| login_id |
+----------+
| hoge     |
| fuga     |
| piyo     |
| hogehoge |
| fugafuga |
| piyopiyo |
+----------+
6 rows in set (0.00 sec)

重複のある値だけを抽出

先に結論から。

SELECT `カラム名` FORM `テーブル名` GROUP BY `カラム名` HAVING COUNT(*) > 1;

mysql> select login_id from users group by login_id having count(*) > 1;
+----------+
| login_id |
+----------+
| fuga     |
| hoge     |
+----------+
2 rows in set (0.01 sec)

ちゃんと重複のある「hoge」と「fuga」だけが出てきました。

こうすると分かりやすいかも。

mysql> select login_id, count(*) from users group by login_id;
+----------+----------+
| login_id | count(*) |
+----------+----------+
| fuga     |        2 |
| fugafuga |        1 |
| hoge     |        2 |
| hogehoge |        1 |
| piyo     |        1 |
| piyopiyo |        1 |
+----------+----------+
6 rows in set (0.00 sec)

↑GROUP BYでグルーピングしたあとに、

mysql> select login_id, count(*) from users group by login_id having count(*) > 1;
+----------+----------+
| login_id | count(*) |
+----------+----------+
| fuga     |        2 |
| hoge     |        2 |
+----------+----------+
2 rows in set (0.00 sec)

↑レコード数が2以上（重複のある）のものに絞って抽出。

HAVINGについて補足

HAVING 句は、WHERE 句が参照できない集約関数を参照できます。

SQL 標準では、HAVING は GROUP BY 句内のカラムか、または集約関数で使用されるカラムしか参照できません。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.9 SELECT 構文

重複のある値のレコードすべてを表示

こちらも先に結論から。

SELECT * FORM `テーブル名` WHERE `カラム名` IN (SELECT `カラム名` FROM `テーブル名` GROUP BY `カラム名` HAVING COUNT(*) > 1);

サブクエリの中身は先ほどと一緒。

GROUP BYで集約するカラム以外の値を表示したいとなると同じSELECT句に含めることはできないので、サブクエリを使用する。

mysql> select id, login_id from users \
    -> where login_id in \
    -> (select login_id from users group by login_id having count(*) > 1);
+----+----------+
| id | login_id |
+----+----------+
|  1 | hoge     |
|  2 | fuga     |
|  7 | hoge     |
|  8 | fuga     |
+----+----------+
4 rows in set (0.01 sec)

ちゃんと全て取り出すことができました。