- MySQL
-
2021-08-26 - 更新:2024-06-05
MySQLのテーブルで指定した列のデータが重複しているレコードを抽出、及び削除する方法をご紹介します。
まずはサンプルのテーブルを用意しましょう。
1 | CREATE TABLE `sample` ( |
2 | `id` int(8) NOT NULL AUTO_INCREMENT, |
3 | `name` varchar(30) NOT NULL, |
4 | `type` int(4) NOT NULL, |
5 | PRIMARY KEY (`id`) |
6 | ); |
サンプルデータを登録します。
1 | INSERT INTO `sample` VALUES |
2 | (DEFAULT, '赤井', 1), |
3 | (DEFAULT, '井田', 2), |
4 | (DEFAULT, '宇野', 3), |
5 | (DEFAULT, '江崎', 1), |
6 | (DEFAULT, '奥本', 2); |
テーブルの中身を見てみると次のようになります。
1 | mysql> select * from `sample`; |
2 | +----+--------+------+ |
3 | | id | name | type | |
4 | +----+--------+------+ |
5 | | 1 | 赤井 | 1 | |
6 | | 2 | 井田 | 2 | |
7 | | 3 | 宇野 | 3 | |
8 | | 4 | 江崎 | 1 | |
9 | | 5 | 奥本 | 2 | |
10 | +----+--------+------+ |
typeに重複したデータがあることが分かります。
それではCOUNT関数とHAVING句を使って重複している件数を確認してみましょう。
1 | mysql> SELECT type, count(*) as cnt FROM `sample` group by type having cnt > 1; |
結果は次のようになります。
1 | +------+-----+ |
2 | | type | cnt | |
3 | +------+-----+ |
4 | | 1 | 2 | |
5 | | 2 | 2 | |
6 | +------+-----+ |
typeが「1」と「2」のデータが2件ずつあることが分かります。
重複データを抽出
まずは重複したデータを抽出してみましょう。
typeが同じでnameが違うデータを抽出します。
1 | SELECT * FROM sample |
2 | WHERE |
3 | EXISTS ( |
4 | SELECT * FROM sample isample |
5 | WHERE |
6 | sample.type = isample.type |
7 | AND sample.name <> isample.name |
8 | ) |
9 | ORDER BY type, id |
10 | ; |
結果は次のようになります。
1 | +----+--------+------+ |
2 | | id | name | type | |
3 | +----+--------+------+ |
4 | | 1 | 赤井 | 1 | |
5 | | 4 | 江崎 | 1 | |
6 | | 2 | 井田 | 2 | |
7 | | 5 | 奥本 | 2 | |
8 | +----+--------+------+ |
データ量が多い場合等、上記クエリでは重くて表示できないときは次のように試してください。
1 | SELECT * FROM sample |
2 | GROUP BY type |
3 | HAVING COUNT(type) > 1 AND COUNT(DISTINCT name) > 1; |
結果には重複が省かれたデータが出力されますが、どのデータが対象かどうかは判断できます。
1 | +----+--------+------+ |
2 | | id | name | type | |
3 | +----+--------+------+ |
4 | | 1 | 赤井 | 1 | |
5 | | 2 | 井田 | 2 | |
6 | +----+--------+------+ |
重複データを削除
今度は重複したデータを削除してみましょう。
サブクエリーを利用して重複したデータを削除する
1 | DELETE FROM `sample` |
2 | WHERE id IN ( |
3 | SELECT id FROM ( |
4 | SELECT id FROM `sample` |
5 | GROUP BY type |
6 | HAVING COUNT(*) > 1 |
7 | ) AS tmp |
8 | ); |
上記のSQLを実行した後のテーブルの中身を見てみましょう。
1 | mysql> select * from `sample`; |
2 | +----+--------+------+ |
3 | | id | name | type | |
4 | +----+--------+------+ |
5 | | 3 | 宇野 | 3 | |
6 | | 4 | 江崎 | 1 | |
7 | | 5 | 奥本 | 2 | |
8 | +----+--------+------+ |
重複したデータが削除されていますね。
削除されるのはidが若い方のデータであることが分かります。
テーブルのデータを整理したいとき等に便利なので、是非使ってみてください。
おまけ
SQLにはDISTINCTという重複した行を排除することが出来る便利なコマンドがあります。
単純に一意なデータが欲しいとか、重複を省いた件数が知りたいなどといった場合に便利です。
重複するデータを省いたデータを表示する。
1 | mysql> SELECT DISTINCT type FROM `sample`; |
2 | +------+ |
3 | | type | |
4 | +------+ |
5 | | 1 | |
6 | | 2 | |
7 | | 3 | |
8 | +------+ |
重複するデータを省いたデータの件数を表示する。
1 | SELECT COUNT(DISTINCT type) result FROM `sample`; |
2 | +--------+ |
3 | | result | |
4 | +--------+ |
5 | | 3 | |
6 | +--------+ |
フィールドは複数指定することも可能です。
1 | SELECT COUNT(DISTINCT name, type) result FROM `sample`; |
この記事がお役に立ちましたらシェアお願いします
4,495 views
Fedoraインストール時にごちゃごちゃになったHDの整理の... (25/02/14)
